CREATE VIEW

Purpose

Use the CREATE VIEW statement to define a view, which is a logical table based on one or more tables or views. A view contains no data itself. The tables upon which a view is based are called base tables.

You can also create an object view or a relational view that supports LOB and object datatypes (object types, REFs, nested table, or varray types) on top of the existing view mechanism. An object view is a view of a user-defined type, where each row contains objects, each object with a unique object identifier.

See Also: Oracle8i Concepts, Oracle8i Application Developer's Guide - Fundamentals, and Oracle8i Administrator's Guidefor information on various types of views and their uses ALTER VIEW for information on modifying a view DROP VIEW for information on removing a view from the database

Prerequisites

To create a view in your own schema, you must have CREATE VIEW system privilege. To create a view in another user's schema, you must have CREATE ANY VIEW system privilege.

The owner of the schema containing the view must have the privileges necessary to either select, insert, update, or delete rows from all the tables or views on which the view is based. The owner must be granted these privileges directly, rather than through a role.

To use the basic constructor method of an object type when creating an object view, one of the following must be true:

• The object type must belong to the same schema as the view to be created.

• You must have EXECUTE ANY TYPE system privileges.

• You must have the EXECUTE object privilege on that object type.

  See Also: SELECT and subquery, INSERT, UPDATE, and DELETE for information on the privileges required by the owner of a view on the base tables or views of the view being created

Partition Views

Partition views were introduced in Release 7.3 to provide partitioning capabilities for applications requiring them. Partition views are supported in Oracle8i so that you can upgrade applications from Release 7.3 without any modification. In most cases, subsequent to migration to Oracle8i you will want to migrate partition views into partitions.

With Oracle8i, you can use the CREATE TABLE statement to create partitioned tables easily. Partitioned tables offer the same advantages as partition views, while also addressing their shortcomings. Oracle recommends that you use partitioned tables rather than partition views in most operational environments.

See Also: Oracle8i Concepts for more information on the shortcomings of partition views Oracle8i Administrator's Guide for information on migrating partition views into partitions CREATE TABLE for more information about partitioned tables

Syntax

subquery: See SELECT and subquery.

with_clause::=

Keywords and Parameters

OR REPLACE

Specify OR REPLACE to re-create the view if it already exists. You can use this clause to change the definition of an existing view without dropping, re-creating, and regranting object privileges previously granted on it.

INSTEAD OF triggers defined in the view are dropped when a view is re-created.

If any materialized views are dependent on view, those materialized views will be marked UNUSABLE and will require a full refresh to restore them to a usable state. Invalid materialized views cannot be used by query rewrite and cannot be refreshed until they are recompiled.

See Also: ALTER MATERIALIZED VIEW for information on refreshing invalid materialized views Oracle8i Concepts for information on materialized views in general CREATE TRIGGER for more information about the INSTEAD OF clause

FORCE

Specify FORCE if you want to create the view regardless of whether the view's base tables or the referenced object types exist or the owner of the schema containing the view has privileges on them. These conditions must be true before any SELECT, INSERT, UPDATE, or DELETE statements can be issued against the view.

NO FORCE

Specify NOFORCE if you want to create the view only if the base tables exist and the owner of the schema containing the view has privileges on them. This is the default.

schema

Specify the schema to contain the view. If you omit schema, Oracle creates the view in your own schema.

view

Specify the name of the view or the object view.

Restriction: If a view has INSTEAD OF triggers, any views created on it must have INSTEAD OF triggers, even if the views are inherently updatable.

alias

Specify names for the expressions selected by the view's query. The number of aliases must match the number of expressions selected by the view. Aliases must follow the rules for naming Oracle schema objects. Aliases must be unique within the view.

If you omit the aliases, Oracle derives them from the columns or column aliases in the view's query. For this reason, you must use aliases if the view's query contains expressions rather than only column names.

Restriction: You cannot specify an alias when creating an object view.

See Also: "Syntax for Schema Objects and Parts in SQL Statements"

OF type_name

Use this clause to explicitly creates an object view of type type_name. The columns of an object view correspond to the top-level attributes of type type_name. Each row will contain an object instance and each instance will be associated with an object identifier (OID) as specified in the WITH OBJECT IDENTIFIER clause. If you omit schema, Oracle creates the object view in your own schema.

WITH OBJECT IDENTIFIER	The WITH OBJECT IDENTIFIER lets you specify the attributes of the object type that will be used as a key to identify each row in the object view. In most cases these attributes correspond to the primary-key columns of the base table. You must ensure that the attribute list is unique and identifies exactly one row in the view. If you try to dereference or pin a primary key REF that resolves to more than one instance in the object view, Oracle raises an error.
	Note: The 8.0 syntax WITH OBJECT OID is replaced with this syntax for clarity. The keywords WITH OBJECT OID are supported for backward compatibility, but Oracle Corporation recommends that you use the new syntax WITH OBJECT IDENTIFIER.
	If the object view is defined on an object table or an object view, you can omit this clause or specify DEFAULT.
	DEFAULT	Specify DEFAULT if you want Oracle to use the intrinsic object identifier of the underlying object table or object view to uniquely identify each row.
	attribute	Specify an attribute of the object type from which Oracle should create the object identifier for the object view.

AS subquery

Specify a subquery that identifies columns and rows of the table(s) that the view is based on. The subquery's select list can contain up to 1000 expressions.

If you create views that refer to remote tables and views, the database links you specify must have been created using the CONNECT TO clause of the CREATE DATABASE LINK statement, and you must qualify them with schema name in the view query.

Restrictions on the view query:

• The view query cannot select the CURRVAL or NEXTVAL pseudocolumns.

• If the view query selects the ROWID, ROWNUM, or LEVEL pseudocolumns, those columns must have aliases in the view query.

• If the view query uses an asterisk (*) to select all columns of a table, and you later add new columns to the table, the view will not contain those columns until you re-create the view by issuing a CREATE OR REPLACE VIEW statement.

• For object views, the number of elements in the view subquery select list must be the same as the number of top-level attributes for the object type. The datatype of each of the selecting elements must be the same as the corresponding top-level attribute.

• You cannot specify the SAMPLE clause.

The preceding restrictions apply to materialized views as well.

• If you want the view to be inherently updatable, it must not contain any of the following constructs:
  • A set operator

  • A DISTINCT operator

  • An aggregate or analytic function

  • A GROUP BY, ORDER BY, CONNECT BY, or START WITH clause

  • A collection expression in a SELECT list

  • A subquery in a SELECT list

  • Joins (with some exceptions). See Oracle8i Administrator's Guide for details.

• If an inherently updatable view contains pseudocolumns or expressions, the UPDATE statement must not refer to any of these pseudocolumns or expressions.

• If you want a join view to be updatable, all of the following conditions must be true:
  • The DML statement must affect only one table underlying the join.

  • For an UPDATE statement, all columns updated must be extracted from a key-preserved table. If the view has the CHECK OPTION, join columns and columns taken from tables that are referenced more than once in the view must be shielded from UPDATE.

  • For a DELETE statement, the join can have one and only one key-preserved table. That table can appear more than once in the join, unless the view has the CHECK OPTION.

  • For an INSERT statement, all columns into which values are inserted must come from a key-preserved table, and the view must not have the CHECK OPTION.

    See Also: Oracle8i Administrator's Guide for more information on updatable views Oracle8i Application Developer's Guide - Fundamentals for more information about updating object views or relational views that support object types

with_clause

Use the with_clause to restrict the subquery in one of the following ways:

WITH READ ONLY	Specify WITH READ ONLY if you want no deletes, inserts, or updates to be performed through the view.
WITH CHECK OPTION	Specify WITH CHECK OPTION to guarantee that inserts and updates performed through the view will result in rows that the view query can select. The CHECK OPTION cannot make this guarantee if: There is a subquery in the query of this view or any view on which this view is based or INSERT, UPDATE, or DELETE operations are performed using INSTEAD OF triggers.
CONSTRAINT constraint	Specify the name of the CHECK OPTION constraint. If you omit this identifier, Oracle automatically assigns the constraint a name of the form SYS_Cn, where n is an integer that makes the constraint name unique within the database.

Examples

Basic View Example

The following statement creates a view of the emptable named dept20. The view shows the employees in Department 20 and their annual salary:

CREATE VIEW dept20 
    AS SELECT ename, sal*12 annual_salary 
        FROM emp 
        WHERE deptno = 20; 

The view declaration need not define a name for the column based on the expression sal*12, because the subquery uses a column alias (annual_salary) for this expression.

Updatable View Example

The following statement creates an updatable view named clerk of all clerks in the emp table. Only the employees' IDs, names, and department numbers are visible in this view and only these columns can be updated in rows identified as clerks:

CREATE VIEW clerk (id_number, person, department, position) 
    AS SELECT empno, ename, deptno, job 
       FROM emp 
       WHERE job = 'CLERK'
    WITH CHECK OPTION CONSTRAINT wco; 

Because of the CHECK OPTION, you cannot subsequently insert a new row into clerk if the new employee is not a clerk.

Join View Example

A join view is one whose view query contains a join. If at least one column in the join has a unique index, then it may be possible to modify one base table in a join view. You can query USER_UPDATABLE_COLUMNS to see whether the columns in a join view are updatable. For example:

CREATE VIEW ed AS
   SELECT e.empno, e.ename, d.deptno, d.loc
      FROM emp e, dept d
   WHERE e.deptno = d.deptno

View created.

SELECT column_name, updatable 
  FROM user_updatable_columns
  WHERE table_name = 'ED';

COLUMN_NAME     UPD
--------------- ---
ENAME           YES
DEPTNO          NO
EMPNO           YES
LOC             NO

INSERT INTO ed (ENAME, EMPNO) values ('BROWN', 1234);

In the above example, there is a unique index on the deptno column of the dept table. You can insert, update or delete a row from the emp base table, because all the columns in the view mapping to the emp table are marked as updatable and because the primary key of emp is included in the view.

Note: You cannot insert into the table using the view unless the view contains all NOT NULL columns of all tables in the join, unless you have specified DEFAULT values for the NOT NULL columns.

See Also: Oracle8i Application Developer's Guide - Fundamentals for more information on updating join views.

Read-Only View Example

The following statement creates a read-only view named clerk of all clerks in the emp table. Only the employees' IDs, names, department numbers, and jobs are visible in this view:

CREATE VIEW clerk (id_number, person, department, position)
     AS SELECT empno, ename, deptno, job 
       FROM emp 
       WHERE job = 'CLERK'
    WITH READ ONLY;

Object View Example

The following example creates object view emp_object_view of employee_type:

CREATE TYPE employee_type AS OBJECT
  ( empno       NUMBER(4),
    ename       VARCHAR2(20), 
    job         VARCHAR2(9), 
    mgr         NUMBER(4),
    hiredate    DATE, 
    sal         NUMBER(7,2), 
    comm        NUMBER(7,2)  );

CREATE OR REPLACE VIEW emp_object_view OF employee_type 
  WITH OBJECT IDENTIFIER (empno)
  AS SELECT empno, ename, job, mgr, hiredate, sal, comm 
     FROM emp;