Purpose

Use the CREATEVIEW 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.

You can also create XMLType views, which are similar to an object views but display data from XMLSchema-based tables of XMLType.

Prerequisites

To create a view in your own schema, you must have CREATEVIEW system privilege. To create a view in another user's schema, you must have CREATEANYVIEW system privilege.

To create a subview, you must have UNDERANYVIEW system privilege or the UNDER object privilege on the superview.

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 EXECUTEANYTYPE system privileges.

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

See Also:

SELECT, 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 Oracle Release 7.3 to provide partitioning capabilities for applications requiring them. Partition views are supported in Oracle9i so that you can upgrade applications from Release 7.3 without any modification. In most cases, subsequent to upgrading to Oracle9i you will want to migrate partition views into partitions.

In Oracle9i, you can use the CREATETABLE 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.

Semantics

OR REPLACE

Specify ORREPLACE 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.

INSTEADOF 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.

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.

If the view definition contains any constraints, CREATEVIEW ... FORCE will fail if the base table does not exist or the referenced object type does not exist. CREATEVIEW ... FORCE will also fail if the view definition references a constraint that does not exist.

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 on Views

If a view has INSTEADOF triggers, any views created on it must have INSTEADOF 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. Also, you must specify aliases if the view definition includes constraints.

Restriction on View Aliases

inline_constraint and out_of_line_constraint

You can specify constraints on views and object views. You define the constraint at the view level using the out_of_line_constraint clause. You define the constraint as part of column or attribute specification using the inline_constraint clause after the appropriate alias.

Oracle does not enforce view constraints. However, operations on views are subject to the integrity constraints defined on the underlying base tables. This means that you can enforce constraints on views through constraints on base tables.

Restrictions on View Constraints

View constraints are a subset of table constraints and are subject to the following restrictions:

You can specify only unique, primary key, and foreign key constraints on views. However, you can define the view using the WITHCHECKOPTION clause, which is equivalent to specifying a check constraint for the view.

Because view constraints are not enforced directly, you cannot specify INITIALLYDEFERRED or DEFERRABLE.

View constraints are supported only in DISABLENOVALIDATE mode. You must specify the keywords DISABLENOVALIDATE when you declare the view constraint, and you cannot specify any other mode.

You cannot specify the using_index_clause, the exceptions_clause clause, or the ONDELETE clause of the references_clause.

object_view_clause

OF type_name Clause

Use this clause to explicitly create 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 WITHOBJECTIDENTIFIER clause. If you omit schema, Oracle creates the object view in your own schema.

Object tables (as well as XMLType tables, object views, and XMLType views) do not have any column names specified for them. Therefore, Oracle defines a system-generated column SYS_NC_ROWINFO$. You can use this column name in queries and to create object views with the WITHOBJECTIDENTIFIER clause.

WITH OBJECT IDENTIFIER Clause

Use the WITHOBJECTIDENTIFIER clause to specify a top-level (root) object view. This clause 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.

Restrictions on Object Views

If you try to dereference or pin a primary key REF that resolves to more than one instance in the object view, Oracle returns an error.

You cannot specify this clause if you are creating a subview, because subviews inherit object identifiers from superviews.

Note:

The Oracle8i, Release 8.0 syntax WITHOBJECTOID is replaced with this syntax for clarity. The keywords WITHOBJECTOID are supported for backward compatibility, but Oracle Corporation recommends that you use the new syntax WITHOBJECTIDENTIFIER.

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

For attribute, specify an attribute of the object type from which Oracle should create the object identifier for the object view.

UNDER Clause

Use the UNDER clause to specify a subview based on an object superview.

To learn whether a view is a superview or a subview, query the SUPERVIEW_NAME column of the USER_, ALL_, or DBA_VIEWS data dictionary views.

Restrictions on Subviews

You must create a subview in the same schema as the superview.

The object type type_name must be the immediate subtype of superview.

You can create only one subview of a particular type under the same superview.

AS subquery

Specify a subquery that identifies columns and rows of the table(s) that the view is based on. The select list of the subquery 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 CONNECTTO clause of the CREATEDATABASELINK statement, and you must qualify them with schema name in the view subquery.

If you create a view with the flashback_clause in the defining subquery, Oracle does not interpret the ASOF expression at create time but rather each time a user subsequently queries the view.

Restrictions on the Defining Subquery of a View

The view subquery cannot select the CURRVAL or NEXTVAL pseudocolumns.

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

If the view subquery 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 CREATEORREPLACEVIEW 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.

Notes on Creating Updatable Views

An updatable view is one you can use to insert, update, or delete base table rows. You can create a view to be inherently updatable, or you can create an INSTEADOF trigger on any view to make it updatable.

To learn whether and in what ways the columns of an inherently updatable view can be modified, query the USER_UPDATABLE_COLUMNS data dictionary view. (The information displayed by this view is meaningful only for inherently updatable views.)

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 GROUPBY, ORDERBY, CONNECTBY, or STARTWITH clause

A collection expression in a SELECT list

A subquery in a SELECT list

Joins (with some exceptions as described in the paragraphs that follow).

In addition, if an inherently updatable view contains pseudocolumns or expressions, you cannot update base table rows with an UPDATE statement that refers 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 INSERT statement, the view must not be created WITHCHECKOPTION, and all columns into which values are inserted must come from a key-preserved table. A key-preserved table in one for which every primary key or unique key value in the base table is also unique in the join view.

For an UPDATE statement, all columns updated must be extracted from a key-preserved table. If the view was created WITHCHECKOPTION, 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, if the join results in more than one key-preserved table, then Oracle deletes from the first table named in the FROM clause, whether or not the view was created WITHCHECKOPTION.

XMLType_view_clause

Use this clause to create an XMLType view, which displays data from an XMLSchema-based table of type XMLType. The XMLSchema_spec indicates the XMLSchema to be used to map the XML data to its object-relational equivalents. The XMLSchema must already have been created before you can create an XMLType view.

Object tables (as well as XMLType tables, object views, and XMLType views) do not have any column names specified for them. Therefore, Oracle defines a system-generated column SYS_NC_ROWINFO$. You can use this column name in queries and to create object views with the WITHOBJECTIDENTIFIER clause.

subquery_restriction_clause

Use the subquery_restriction_clause to restrict the defining subquery of the view in one of the following ways:

WITH READ ONLY

Specify WITHREADONLY to indicate that the table or view cannot be updated.

WITH CHECK OPTION

Specify WITHCHECKOPTION to indicate that Oracle prohibits any changes to the table or view that would produce rows that are not included in the subquery.

CONSTRAINT constraint

Specify the name of the CHECKOPTION 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.

Note on WITH CHECK OPTION:

For tables, WITHCHECKOPTION guarantees that inserts and updates result in tables that the defining table subquery can select. For views, WITHCHECKOPTION cannot make this guarantee if:

There is a subquery within the defining subquery of this view or any view on which this view is based or

INSERT, UPDATE, or DELETE operations are performed using INSTEADOF triggers.

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

Creating a View with Constraints: Example

The following statement creates a restricted view of the sample table hr.employees and defines a unique constraint on the email view column and a primary key constraint for the view on the emp_id view column:

Creating an Updatable View: Example

The following statement creates an updatable view named clerk of all sales and purchasing clerks in the employees table. Only the employees' IDs, last names, department numbers, and jobs are visible in this view, and these columns can be updated only in rows where the employee is a king of clerk:

This view lets you change the job_id of a purchasing clerk to purchasing manager (PU_MAN):

UPDATE clerk SET job_id = 'PU_MAN' WHERE employee_id = 118;

The next example creates the same view WITHCHECKOPTION. You cannot subsequently insert a new row into clerk if the new employee is not a clerk. You can update an employee's job_id from one type of clerk to another type of clerk, but the update in the preceding statement would fail, because the view cannot access employees with non-clerk job_id.

Creating a Join View: Example

A join view is one whose view subquery 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:

In the preceding example, the primary key index on the location_id column of the locations table is not unique in the locations_view view. Therefore, locations is not a key-preserved table and columns from that base table are not updatable.

INSERT INTO locations_view VALUES
(999, 'Entertainment', 87, 'Roma');
INSERT INTO locations_view VALUES
*
ERROR at line 1:
ORA-01776: cannot modify more than one base table through a join
view

You can insert, update, or delete a row from the departments base table, because all the columns in the view mapping to the departments table are marked as updatable and because the primary key of departments is retained in the view.

Creating an XMLType View: Example

In some cases you may have an object-relational table upon which you would like to build an XMLType view. The following example creates an object-relational table (resembling the XMLType column warehouse_spec in the sample table oe.warehouses), and then creates an XMLType view of that table: