3.4 Managing Views

A view is a logical representation of another table or combination of tables. Use Object Browser to create, view, edit, download, compile, and drop views.

A view does not contain or store data but derives its data from the views or tables on which it is based. These tables are called base tables. All operations performed on a view affect the base table of the view. In order to be updatable, a view cannot contain any of the following constructs: SET or DISTINCT, operators, aggregate or analytic functions, GROUP BY, ORDER BY, CONNECT BY, START WITH clauses, subqueries, or collection expressions in a SELECT list.

For an example where using a view might be preferable to a table, consider the HR.EMPLOYEES table, which has several columns and numerous rows. The staff view allows users to see only five of these columns:

CREATE VIEW staff AS
SELECT employee_id, last_name, job_id, manager_id, department_id
FROM employees

• Creating a View
  Create a view using Object Browser.
• Viewing a View
  Select a view from the Object Tree and access tabs in the Object Detail View.
• Editing a View Manually
  Select a view from the Object Tree and edit the view in Code Editor.
• Downloading a View
  Select a view from the Object Tree and click Download to save it as a file.
• Compiling a View
  Select a view from the Object Tree and click Save and Compile.
• Dropping a View
  Select a view from the Object Tree and click Drop.

See Also:

Managing Views in Oracle Database Administrator’s Guide

3.4.1 Creating a View

Create a view using Object Browser.

To create a view:

1. In Object Browser, click the Create Database Objects menu and select View.

   Tip:

   To create new objects from the Object Tree, right-click the object and select the Create option.

   The Create View Wizard appears.

2. View Name - Enter the name of the view. The name must conform to Oracle naming conventions and cannot contain spaces, or start with a number or underscore.
3. SQL Query - Enter a valid SQL query for the view. SQL Query features a functional Code Editor.

   Tip:

   To learn more about the Code Editor, including Query Builder, see Code Editor in Object Browser.
4. Click one of the following:
   • Click Preview SQL. Review the SQL script used to generate the table and then click Create View.
   • Click Create View.

See Also:

Using Query Builder

3.4.2 Viewing a View

Select a view from the Object Tree and access tabs in the Object Detail View.

To browse a view:

1. In Object Browser, Object Tree, expand Views and select a view.
2. The Object Detail View appears and displays the following tabs:
   • Columns - Displays the columns in the current view. Available actions include:
     • Compile
     • Drop
     • Comment.
     • Refresh
   • Code - Displays the code that creates this view. You can edit the code using the fully functional Code Editor. Available actions include:
     • Download
     • Drop.
     • Save and Compile
     • Refresh
   • Data - Displays the data in the columns in this view. Available actions include:
     • Columns - Select the columns to display.
     • Filter - Create filters by selecting the column, operator, and value.
     • Count Rows - Count rows in the view.
     • Download
     • Refresh
   • Errors - Lists errors related to the current view. Available actions include:
     • Refresh
   • Grants - Details of grants for the current view, including privilege, grantee, grantable, grantor, and object name. Available actions include:
     • Grant
     • Revoke
     • Refresh
   • Dependencies - Displays objects referenced by this view, objects this view references, and synonyms for this view. Available actions:
     • Refresh
   • DDL - Displays the SQL necessary to re-create this view. Available actions include:
     • Download
     • Refresh

See Also:

Code Editor in Object Browser

3.4.3 Editing a View Manually

Select a view from the Object Tree and edit the view in Code Editor.

To edit a view manually:

1. In Object Browser, Object Tree, expand Views and select a view.

   The Object Detail View appears.

2. Select the Code tab and edit the source code directly using the Code Editor.

   Tip:

   To learn more about the Code Editor, see Code Editor in Object Browser.

   If you edit and make changes to a view, you must compile to save your changes.

3. Click Save and Compile to compile the view.

   Compiling re-creates the object in the database. If the compilation fails, an error message displays above the code.

3.4.4 Downloading a View

Select a view from the Object Tree and click Download to save it as a file.

When working with views, Download is available on the Code and DDL tabs in Ojbect Detail View.

To save the current view as a file:

1. In Object Browser, Object Tree, expand Views and select a view.

   The Object Detail View appears.

2. To download the code that creates the view:
   1. Click the Code tab.
   2. Click the Download.
3. To download the DDL:
   1. Click the DDL tab.
   2. Click the Download.

3.4.5 Compiling a View

Select a view from the Object Tree and click Save and Compile.

If you edit and make changes to a view, you must compile to save your changes.

To compile the current view:

1. In Object Browser, Object Tree, expand Views and select a view.

   The Object Detail View appears.

2. Click the Code tab and then Save and Compile.

   Compiling re-creates the object in the database. If the compilation fails, an error message displays above the code.

3.4.6 Dropping a View

Select a view from the Object Tree and click Drop.

The Drop action is available on the Columns and Code tabs in Object Detail View.

To drop a view:

1. In Object Browser, Object Tree, expand Views and select a view.

   The Object Detail View appears.

2. Select either the Columns or Code tab and then click Drop.

   In the Drop dialog:

   1. Drop - Review the details.
   2. SQL - Displays the SQL generated to drop the view. To copy the displayed SQL, click the Copy icon.
   3. To confirm your selection, click Drop again.