- About Deploying Opaque Views
- Deploying Opaque View Objects
- Undeploying a Deployed View
- Guidelines for Deleting an Opaque View or Deployed View
- Guidelines for Redeploying Opaque Views
An opaque view is a physical layer table that consists of a Select statement. When you need a new table, you should create a physical table or a materialized view. An opaque view should be used only if there is no other solution.
In the repository, opaque views appear as view tables in the physical databases but the view does not actually exist. You deploy an opaque view in the physical database using the Deploy View(s) utility. After deploying an opaque view, it is called a deployed view. Opaque views can be used without deploying them but the Oracle BI Server has to generate a more complex query when an opaque view is encountered.
NOTE: Databases such as XLS and nonrelational database do not support opaque views and, therefore, cannot run the view deployment utility.
To verify opaque views are supported by a database, make sure that the CREATE_VIEW_SUPPORTED SQL feature is selected in the Database dialog box, in the Features tab. For instructions, refer to Specifying SQL Features Supported by a Database.
In offline mode, the Deploy View(s) utility is available when importing from databases with ODBC and DB2cli data sources. Oracle Native (client) drivers are also supported in the offline mode for deploying views. In online mode, view deployment is available for supported databases using Import through server (the settings on the client will be ignored).
The SQL statement for deploying opaque views in the physical layer of the repository is available for supported databases. To determine which of your databases support opaque views, contact your system administrator or consult your database documentation.
Only repository variables can be used in the definition. An error will generate if a session variable is used in the view definition.
The user-entered SQL in the opaque view object. If SQL is invalid, the create view statement will fail during view deployment.
For opaque view objects, the right-click menu contains the Deploy View(s) option. When you select Deploy View(s), the Create View SQL statement executes and attempts to create the deployed view objects. The following list describes the ways you can initiate view deployment and the results of each method:
- Right-click a single opaque view object. When you select Deploy View(s), the Create View SQL statement executes and attempts to create a deployed view for the object.
- Right-click several objects. If at least one of the selected objects is an opaque view object, the right-click menu contains the Deploy View(s) option. When you select Deploy View(s), the Create View SQL statement executes and attempts to create the deployed views for any qualifying objects.
- Right-click a physical schema or physical catalog. If any opaque view object exists in the schema or catalog, the right-click menu contains the Deploy View(s) option. When you select Deploy View(s), the Create View SQL statements for all qualifying objects execute and attempt to create deployed views for the qualifying objects contained in the selected schema or catalog.
During deployment, names are assigned to the views. If you change the preassigned name, the new name must be alphanumeric and no more than 18 characters. If these guidelines are not followed, the object name will be automatically transformed to a valid name using the following Name Transform algorithm:
- All non-alphanumeric characters will be removed.
- If there are 16 or more after Step 1, the first 16 characters will be kept.
- Two digits starting from 00 to 99 will be appended to the name to make the name unique in the corresponding context.
- Views that have been successfully and unsuccessfully deployed appear in a list.
- For unsuccessful deployments, a brief reason appears in the list.
- If deployment is successful, the object type of the opaque view changes from Select to None and the deployed view will be treated as a regular table. NOTE: If you change the type back to Select, the associated opaque views will be dropped from database or an error message will appear. For information about deleting deployed views, refer to Guidelines for Deleting an Opaque View or Deployed View
- In the Administration Tool, the view icon changes to the deployed view icon for successfully deployed views.
- In the Physical layer of the repository, right-click the opaque view that you want to deploy.
- In the right-click menu, choose Deploy View(s).
- In the View Deployment - Deploy View(s) dialog box, perform the following steps:
- If there are multiple connection pools defined for the database, in the Select Connection Pool dialog box, choose a connection pool and click Select.
- In the View Deployment Messages dialog box, you can search for views using Find and Find Again or copy the contents.
- When you have performed the desired tasks, click OK.
Running the Undeploy View(s) utility against a deployed view deletes the views and converts the table back to an opaque view with its original SELECT statement.
- In the Physical layer of the repository, right-click a database, physical catalog, schema, or physical table.
- Choose Undeploy View(s).
- If you do not wish to undeploy one or more of the views at this time, in the appropriate rows, clear the check boxes.
- View Deployment - Undeploy View(s) dialog box, click OK to remove the views.
- In the View Deployment Messages dialog box, you can search for undeployed views using Find and Find Again, or you can copy the contents.
- When you have performed the desired tasks, click OK.
- Removing an undeployed opaque view in the repository. If the opaque view has not been deployed, you can delete it from the repository.
- Removing a deployed view. When you deploy an opaque view, a database view table is created physically in the back-end database and the repository. Therefore, you must undeploy the view before deleting it. You use the Undeploy View(s) utility in the Administration Tool. This removes the opaque view from the back-end database, changes the Object Type from None to Select, and restores the SELECT statement of the object in the physical layer of repository.
After removing an opaque view, you can choose to redeploy it. The Administration Tool does not distinguish between a first-time deployment and a redeployment. Make sure that you remove a deployed view before deploying the opaque view again. Failure to do this causes the deploy operation to fail, and an error message will be returned from the database