Managing Views

All operations performed on a view actually affect the base table of the view. You can use views in almost the same way as tables. You can query, update, insert into, and delete from views, just as you can standard tables.

Views can provide a different representation (such as subsets or supersets) of the data that resides within other tables and views. Views are very powerful because they allow you to tailor the presentation of data to different types of users.

The following sections explain how to create, replace, and drop views using SQL commands.

Creating Views

CREATE VIEW sales_staff AS
   SELECT empno, ename, deptno
   FROM emp
   WHERE deptno = 10
   WITH CHECK OPTION CONSTRAINT sales_staff_cnst;

The query that defines the SALES_STAFF view references only rows in department 10. Furthermore, the WITH CHECK OPTION creates the view with the constraint that INSERT and UPDATE statements issued against the view are not allowed to create or result in rows that the view cannot select. Considering the example above, the following INSERT statement successfully inserts a row into the EMP table via the SALES_STAFF view:

INSERT INTO sales_staff VALUES (7584, 'OSTER', 10);

However, the following INSERT statement is rolled back and returns an error because it attempts to insert a row for department number 30, which could not be selected using the SALES_STAFF view:

INSERT INTO sales_staff VALUES (7591, 'WILLIAMS', 30);

The following statement creates a view that joins data from the EMP and DEPT tables:

CREATE VIEW division1_staff AS
   SELECT ename, empno, job, dname
   FROM emp, dept
   WHERE emp.deptno IN (10, 30)
   AND emp.deptno = dept.deptno;

The DIVISION1_STAFF view is defined by a query that joins information from the EMP and DEPT tables. The WITH CHECK OPTION is not specified in the CREATE VIEW statement because rows cannot be inserted into or updated in a view defined with a query that contains a join that uses the WITH CHECK OPTION; see and .

Expansion of Defining Queries at View Creation Time

As an example, assume that the DEPT view is created as follows:

CREATE VIEW dept AS SELECT * FROM scott.dept;

Oracle stores the defining query of the DEPT view as

SELECT "DEPTNO", "DNAME", "LOC" FROM scott.dept

Views created with errors do not have wildcards expanded. However, if the view is eventually compiled without errors, wildcards in the defining query are expanded.

Creating Views with Errors

You can only create a view with errors by using the FORCE option of the CREATE VIEW command:

CREATE FORCE VIEW AS ...;

When a view is created with errors, Oracle returns a message that indicates the view was created with errors. The status of such a view is left as INVALID. If conditions later change so that the query of an invalid view can be executed, the view can be recompiled and become valid. Oracle dynamically compiles the invalid view if you attempt to use it.

Privileges Required to Create a View

• You must have the CREATE VIEW system privilege to create a view in your schema or the CREATE ANY VIEW system privilege to create a view in another user's schema. These privileges can be acquired explicitly or via a role.

• The owner of the view must have been explicitly granted the necessary privileges to access all objects referenced within the definition of the view; the owner cannot have obtained the required privileges through roles. Also, the functionality of the view is dependent on the privileges of the view's owner. For example, if you (the view owner) are granted only the INSERT privilege for Scott's EMP table, you can create a view on his EMP table, but you can only use this view to insert new rows into the EMP table.

• If the view owner intends to grant access to the view to other users, the owner must have received the object privileges to the base objects with the GRANT OPTION or the system privileges with the ADMIN OPTION; if not, the view owner has insufficient privileges to grant access to the view to other users.

Replacing Views

• A view can be dropped and then re-created. When a view is dropped, all grants of corresponding view privileges are revoked from roles and users. After the view is re-created, necessary privileges must be regranted.

• A view can be replaced by redefining it with a CREATE VIEW statement that contains the OR REPLACE option. This option is used to replace the current definition of a view but preserve the present security authorizations. For example, assume that you create the SALES_STAFF view, as given in a previous example. You also grant several object privileges to roles and other users. However, now you realize that you must redefine the SALES_STAFF view to correct the department number specified in the WHERE clause of the defining query, because it should have been 30. To preserve the grants of object privileges that you have made, you can replace the current version of the SALES_STAFF view with the following statement:

      CREATE OR REPLACE VIEW sales_staff AS
          SELECT empno, ename, deptno
          FROM emp
          WHERE deptno = 30
          WITH CHECK OPTION CONSTRAINT sales_staff_cnst;

Replacing a view has the following effects:

• Replacing a view replaces the view's definition in the data dictionary. All underlying objects referenced by the view are not affected.

• If previously defined but not included in the new view definition, the constraint associated with the WITH CHECK OPTION for a view's definition is dropped.

• All views and PL/SQL program units dependent on a replaced view become invalid.

Privileges Required to Replace a View

Using Views

SELECT * FROM division1_staff;

ENAME           EMPNO JOB       DNAME
---------- ---------- --------- --------------
CLARK            7782 MANAGER   ACCOUNTING
KING             7839 PRESIDENT ACCOUNTING
MILLER           7934 CLERK     ACCOUNTING
ALLEN            7499 SALESMAN  SALES
WARD             7521 SALESMAN  SALES
JAMES            7900 CLERK     SALES
TURNER           7844 SALESMAN  SALES
MARTIN           7654 SALESMAN  SALES
BLAKE            7698 MANAGER   SALES

With some restrictions, rows can be inserted into, updated in, or deleted from a base table using a view. The following statement inserts a new row into the EMP table using the SALES_STAFF view:

INSERT INTO sales_staff
   VALUES (7954, 'OSTER', 30);

Restrictions on DML operations for views use the following criteria in the order listed:

1. If a view is defined by a query that contains SET or DISTINCT operators, a GROUP BY clause, or a group function, rows cannot be inserted into, updated in, or deleted from the base tables using the view.

2. If a view is defined with the WITH CHECK OPTION, a row cannot be inserted into, or updated in, the base table (using the view) if the view cannot select the row from the base table.

3. If a NOT NULL column that does not have a DEFAULT clause is omitted from the view, a row cannot be inserted into the base table using the view.

4. If the view was created by using an expression, such as DECODE(deptno, 10, 'SALES', ...), rows cannot be inserted into or updated in the base table using the view.

CREATE VIEW sales_staff AS
   SELECT empno, ename
   FROM emp
   WHERE deptno = 10
   WITH CHECK OPTION CONSTRAINT sales_staff_cnst;

Considering this view definition, you can update the EMPNO or ENAME fields of existing records, but you cannot insert rows into the EMP table via the SALES_STAFF view because the view does not let you alter the DEPTNO field. If you had defined a DEFAULT value of 10 on the DEPTNO field, you could perform inserts.

Referencing Invalid Views When a user attempts to reference an invalid view, Oracle returns an error message to the user:

ORA-04063: view 'view_name' has errors

This error message is returned when a view exists but is unusable due to errors in its query (whether it had errors when originally created or it was created successfully but became unusable later because underlying objects were altered or dropped).

Privileges Required to Use a View

Dropping Views

DROP VIEW sales_staff;

Privileges Required to Drop a View