Managing Views

A view is a logical representation of another table or combination of tables. 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

Topics:

Creating a View

To create a view:

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

Object Browser appears.
Click Create.
From the list of object types, select View.
Define the view:
- View Name - Enter a name for the View.
- Query - Specify a query to define the view.
  
  To access Query Builder or SQL Command Processor, click the appropriate link at the bottom of the page. The selected tool appears 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.
Click Next.

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

Browsing a 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:

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

Object Browser appears.
From the Object list, select Views.
From the Object Selection pane, select a view.

The view definition appears displaying the appropriate columns.

Summary of Available Views

Click the tabs at the top of the page to view different reports. Table 1-3 describes all available views.

Table 1-3 Available Views for Views

View	Description
View	(Default) Displays the columns in the current view. Actions you can perform include: Compile Drop See Also: "Editing a View", "Compiling a View", and "Dropping a View"
Code	Displays the code editor. See Also: "Editing a View".
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. See Also: "Managing User Interface Defaults"
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.

Editing a View

When you edit a view you can edit the code manually, perform a search and replace, and compile the view. Additionally, you can save the view as a file or drop it.

Editing a View Manually

To edit a view manually:

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

Object Browser appears.
From the Object list, select Views.
From the Object Selection pane, select a view.
Select the Code tab.

The code editor appears.

If you edit and make changes to a view, you must compile. See "Compiling a View".

Using Find and Replace

Click Find & Replace to perform a basic search and replace.

Downloading a View

Click Download Source to save the current view as a file.

Compiling a View

If you edit and make changes to a view, you must compile to save your changes. Note that there is no save function since this is just a view of the object within the database.

Click Save & Compile to re-create the current view.

Dropping a View

To drop a view:

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

Object Browser appears.
From the Object list, select Views.
From the Object Selection pane, select a view.
Select the View tab or the Code tab.
Click Drop.
To confirm, click Finish.