3.4 Managing Views

A view is a logical representation of another table or combination of tables. You can create, browse, edit, download, compile, drop a view and view reports.

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 clause, subquery, or collection expression 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. To allow users to see only five of these columns or only specific rows, a view is created as follows:

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.
• Browsing a View
  Select a view from the Object Selection pane and view different reports about the view.
• Reports for Views
  Alternative views available when browsing a view in Object Browser.
• Editing a View Manually
  When you edit a view you can edit the code manually, perform a search and replace, and compile the view.
• Using Find and Replace
  Select a view from the Object Selection pane and click Find or Replace.
• Downloading a View
  Select a view from the Object Selection pane and download a view by clicking Download Source.
• Compiling a View
  Click the Save & Compile button to save changes to the view.
• Dropping a View
  Select a view from the Object Selection pane, select the View or Code tab, and click Drop.

See Also:

Oracle Database Administrator’s Guide

3.4.1 Creating a View

Create a view using Object Browser.

To create a view:

1. On the Workspace home page, click SQL Workshop and then Object Browser.

   Object Browser appears.

2. Click the Create icon.
3. From the list of object types, select View.
4. Define the view:

   • View Name - Enter a name for the View.

   • Query - Enter a query to define the view.

   Tip:

   To access Query Builder or SQL Commands, click the appropriate button at the bottom of the page. The selected tool displays in a pop-up window. Once you create the appropriate SQL, click Return to automatically close the popup window and return to the wizard with the SQL.

5. Click Next.

   A confirmation page appears, which displays the SQL used to create the view.

6. Click Create View.

See Also:

• "Using Query Builder"

• "Using SQL Commands"

3.4.2 Browsing a View

Select a view from the Object Selection pane and view different reports about the view.

When you access a view in Object Browser, the Detail pane displays a report listing the columns in that view.

To browse a view:

1. On the Workspace home page, click SQL Workshop and then Object Browser.

   Object Browser appears.

2. From the Object list, select Views.
3. From the Object Selection pane, select a view.

   The view definition appears displaying the appropriate columns.

4. Click the tabs at the top of the page to view different reports about the view.

See Also:

"Reports for Views"

3.4.3 Reports for Views

Alternative views available when browsing a view in Object Browser.

Table 3-3 describes all available reports for views.

Table 3-3 Available Reports for Views

View	Description
View	(Default) Displays the columns in the current view. Actions you can perform include: Compile Drop
Code	Displays the code editor.
Data	Displays a report of the data in the columns in the view. Actions you can perform include: Query - Enables you to sort by column. To restrict specific rows, enter a condition in the Column Condition field. Use the percent sign (%) for wildcards. From Order by, select the columns you want to review and click Query. Count Rows - Enables you to count rows in the table. Insert Row - Enables you to insert a row into the table.
Grants	Displays a list of grants associated with the columns in the view. Grant details include grantee, privilege, and grant options. Actions you can perform include Grant and Revoke.
UI Defaults	Displays user interface defaults for forms and reports. User interface defaults enable developers to assign default user interface properties to a table, column, or view within a specified schema. Click Edit to edit existing user interface defaults. Click Create Defaults to initialize user interface defaults for views that do not currently have user interface defaults defined.
Dependencies	Displays a report showing objects referenced by this view, objects this view references, and synonyms for this view.
SQL	Displays the SQL necessary to re-create this view.

See Also:

• "Editing a View Manually"

• "Compiling a View"

• "Dropping a View"

• "Managing User Interface Defaults"

3.4.4 Editing a View Manually

When you edit a view you can edit the code manually, perform a search and replace, and compile the view.

You can save the view as a file or drop it.

To edit a view manually:

1. On the Workspace home page, click SQL Workshop and then Object Browser.

   Object Browser appears.

2. From the Object list, select Views.
3. From the Object Selection pane, select a view.
4. Select the Code tab.

   The code editor appears.

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

See Also:

"Compiling a View."

3.4.5 Using Find and Replace

Select a view from the Object Selection pane and click Find or Replace.

To use Find and Replace:

1. On the Workspace home page, click SQL Workshop and then Object Browser.

   Object Browser appears.

2. From the Object list, select Views.
3. From the Object Selection pane, select a view.
4. To perform a basic search, click the Find icon (which resembles a magnifying glass).
5. To replace existing code, click the Replace icon (which resembles a two-sided arrow).

3.4.6 Downloading a View

Select a view from the Object Selection pane and download a view by clicking Download Source.

To save the current view as a file:

1. On the Workspace home page, click SQL Workshop and then Object Browser.

   Object Browser appears.

2. From the Object list, select Views.
3. From the Object Selection pane, select a view.
4. Select the Code tab.

   The code editor appears.

5. Click Download Source.

3.4.7 Compiling a View

Click the Save & Compile button to save changes to the view.

There is no save function since this is just a view of the object within the database.

To re-create the current view:

1. On the Workspace home page, click SQL Workshop and then Object Browser.

   Object Browser appears.

2. From the Object list, select Views.
3. From the Object Selection pane, select a view.
4. Select the Code tab.

   The code editor appears.

5. Click Save & Compile.

3.4.8 Dropping a View

Select a view from the Object Selection pane, select the View or Code tab, and click Drop.

To drop a view:

1. On the Workspace home page, click SQL Workshop and then Object Browser.

   Object Browser appears.

2. From the Object list, select Views.
3. From the Object Selection pane, select a view.
4. Select the View tab or the Code tab.
5. Click Drop.
6. To confirm, click Finish.