Managing Views

This section describes aspects of managing views, and includes the following topics:

• Creating Views

• Modifying a Join View

• Replacing Views

• Dropping Views

Creating Views

• To create a view in your schema, you must have the CREATE VIEW privilege; to create a view in another user's schema, you must have the CREATE ANY VIEW system privilege. You may acquire these privileges explicitly or via a role.

• The owner of the view (whether it is you or another user) must have been explicitly granted privileges to access all objects referenced in the view definition; the owner cannot have obtained these privileges through roles. Also, the functionality of the view is dependent on the privileges of the view's owner. For example, if the owner of the view has only the INSERT privilege for Scott's EMP table, the view can only be used to insert new rows into the EMP table, not to SELECT, UPDATE, or DELETE rows from it.

• If the owner of the view 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.

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 CHECK OPTION creates the view with the constraint that INSERT and UPDATE statements issued against the view cannot result in rows that the view cannot select. For example, the following INSERT statement successfully inserts a row into the EMP table by means of the SALES_STAFF view, which contains all rows with department number 10:

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 joins information from the EMP and DEPT tables. The CHECK OPTION is not specified in the CREATE VIEW statement for this view.

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

To create a view with errors, you must include the FORCE option of the CREATE VIEW command:

CREATE FORCE VIEW AS ....;

By default, views are not created with errors. When a view is created with errors, Oracle returns a message indicating the view was created with errors. The status of a view created with errors is INVALID. If conditions later change so that the query of an invalid view can be executed, the view can be recompiled and become valid (usable).

See Also: For information changing conditions and their impact on views, see "Managing Object Dependencies" .

Modifying a Join View

• DISTINCT operator

• aggregate functions: AVG, COUNT, GLB, MAX, MIN, STDDEV, SUM, or VARIANCE

• set operations: UNION, UNION ALL, INTERSECT, MINUS

• GROUP BY or HAVING clauses

• START WITH or CONNECT BY clauses

• ROWNUM pseudocolumn

The examples in following sections use the EMP and DEPT tables. These examples work only if you explicitly define the primary and foreign keys in these tables, or define unique indexes. Following are the appropriately constrained table definitions for EMP and DEPT:

CREATE TABLE dept (
        deptno   NUMBER(4) PRIMARY KEY,
        dname    VARCHAR2(14),
        loc      VARCHAR2(13));

CREATE TABLE emp (
        empno    NUMBER(4) PRIMARY KEY,
        ename    VARCHAR2(10),
        job      varchar2(9),
        mgr      NUMBER(4),
        hiredate DATE,
        sal      NUMBER(7,2),
        comm     NUMBER(7,2),
        deptno   NUMBER(2),
        FOREIGN KEY (DEPTNO) REFERENCES DEPT(DEPTNO));.

You could also omit the primary and foreign key constraints listed above, and create a UNIQUE INDEX on DEPT (DEPTNO) to make the following examples work.

See Also: For more information about mergeable views see Chapter 5 in the Oracle7 Server Tuning manual.

Key-Preserved Tables

Note: It is not necessary that the key or keys of a table be selected for it to be key preserved. It is sufficient that if the key or keys were selected, then they would also be key(s) of the result of the join.

Attention: The key-preserving property of a table does not depend on the actual data in the table. It is, rather, a property of its schema and not of the data in the table. For example, if in the EMP table there was at most one employee in each department, then DEPT.DEPTNO would be unique in the result of a join of EMP and DEPT, but DEPT would still not be a key-preserved table.

If you SELECT all rows from EMP_DEPT_VIEW, the results are:

EMPNO      ENAME      DEPTNO     DNAME          LOC 
---------- ---------- ---------- -------------- -----
      7782 CLARK              10 ACCOUNTING     NEW YORK
      7839 KING               10 ACCOUNTING     NEW YORK
      7934 MILLER             10 ACCOUNTING     NEW YORK
      7369 SMITH              20 RESEARCH       DALLAS
      7876 ADAMS              20 RESEARCH       DALLAS
      7902 FORD               20 RESEARCH       DALLAS
      7788 SCOTT              20 RESEARCH       DALLAS
      7566 JONES              20 RESEARCH       DALLAS
8 rows selected.

In this view, EMP is a key-preserved table, because EMPNO is a key of the EMP table, and also a key of the result of the join. DEPT is not a key-preserved table, because although DEPTNO is a key of the DEPT table, it is not a key of the join.

DML Statements and Join Views

UPDATE Statements The following example shows an UPDATE statement that successfully modifies the EMP_DEPT view:

UPDATE emp_dept
  SET sal = sal * 1.10 
    WHERE deptno = 10;

The following UPDATE statement would be disallowed on the EMP_DEPT view:

UPDATE emp_dept
  SET loc = 'BOSTON'
    WHERE ename = 'SMITH';

This statement fails with an ORA-01779 error (``cannot modify a column which maps to a non key-preserved table''), because it attempts to modify the underlying DEPT table, and the DEPT table is not key preserved in the EMP_DEPT view.

In general, all modifiable columns of a join view must map to columns of a key-preserved table. If the view is defined using the WITH CHECK OPTION clause, then all join columns and all columns of repeated tables are not modifiable.

So, for example, if the EMP_DEPT view were defined using WITH CHECK OPTION, the following UPDATE statement would fail:

UPDATE emp_dept
  SET deptno = 10
    WHERE ename = 'SMITH';

The statement fails because it is trying to update a join column.

DELETE Statements You can delete from a join view provided there is one and only one key-preserved table in the join. The following DELETE statement works on the EMP_DEPT view:

DELETE FROM emp_dept
  WHERE ename = 'SMITH';

This DELETE statement on the EMP_DEPT view is legal because it can be translated to a DELETE operation on the base EMP table, and because the EMP table is the only key-preserved table in the join.

In the following view, a DELETE operation cannot be performed on the view because both E1 and E2 are key-preserved tables:

CREATE VIEW emp_emp AS
  SELECT e1.ename, e2.empno, deptno
    FROM emp e1, emp e2
    WHERE e1.empno = e2.empno;

If a view is defined using the WITH CHECK OPTION clause and the key-preserved table is repeated, then rows cannot be deleted from such a view:

CREATE VIEW emp_mgr AS
  SELECT e1.ename, e2.ename mname
    FROM emp e1, emp e2
      WHERE e1.mgr = e2.empno
      WITH CHECK OPTION;

No deletion can be performed on this view because the view involves a self-join of the table that is key preserved.

INSERT Statements The following INSERT statement on the EMP_DEPT view succeeds:

INSERT INTO emp_dept (ename, empno, deptno)
  VALUES ('KURODA', 9010, 40);

This statement works because only one key-preserved base table is being modified (EMP), and 40 is a valid DEPTNO in the DEPT table (thus satisfying the FOREIGN KEY integrity constraint on the EMP table).

An INSERT statement like the following would fail for the same reason that such an UPDATE on the base EMP table would fail: the FOREIGN KEY integrity constraint on the EMP table is violated.

INSERT INTO emp_dept (ename, empno, deptno)
  VALUES ('KURODA', 9010, 77);

The following INSERT statement would fail with an ORA-01776 error (``cannot modify more than one base table through a view'').

INSERT INTO emp_dept (empno, ename, loc)
  VALUES (9010, 'KURODA', 'BOSTON');

An INSERT cannot, implicitly or explicitly, refer to columns of a non-key-preserved table. If the join view is defined using the WITH CHECK OPTION clause, then you cannot perform an INSERT to it.

Using the UPDATABLE_ COLUMNS Views

View Name	Description
USER_UPDATABLE_COLUMNS	Shows all columns in all tables and views in the user's schema that are modifiable.
DBA_UPDATABLE_COLUMNS	Shows all columns in all tables and views in the DBA schema that are modifiable.
ALL_UPDATABLE_VIEWS	Shows all columns in all tables and views that are modifiable.

Table 12 - 1. UPDATABLE_COLUMNS Views

Replacing Views

• You can drop and re-create the view.

• You can redefine the view with a CREATE VIEW statement that contains the OR REPLACE option. The OR REPLACE option replaces the current definition of a view and preserves the current security authorizations. For example, assume that you create the SALES_STAFF view as given in the previous example, and grant several object privileges to roles and other users. However, now you need to redefine the SALES_STAFF view to change the department number specified in the WHERE clause. 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;

Before replacing a view, consider 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 a constraint in the CHECK OPTION was previously defined but not included in the new view definition, the constraint is dropped.

• All views and PL/SQL program units dependent on a replaced view become invalid (not usable). See for more information on how Oracle manages such dependencies.

Dropping Views

DROP VIEW sales_staff;