Skip Headers
Oracle® Database SQL Reference
10g Release 2 (10.2)

Part Number B14200-02
Go to Documentation Home
Home
Go to Book List
Book List
Go to Table of Contents
Contents
Go to Index
Index
Go to Master Index
Master Index
Go to Feedback page
Contact Us

Go to previous page
Previous
Go to next page
Next
PDF · Mobi · ePub

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 LOBs, object types, REF datatypes, 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 updatableobject identifier.

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

See Also:

Prerequisites

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

To create a subview, you must have the UNDER ANY VIEW 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:

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

Syntax

create_view::=

Description of create_view.gif follows
Description of the illustration create_view.gif

(inline_constraint::= and out_of_line_constraint::=, object_view_clause::=, XMLType_view_clause ::=, subquery::=--part of SELECT, subquery_restriction_clause::=)

object_view_clause::=

Description of object_view_clause.gif follows
Description of the illustration object_view_clause.gif

(inline_constraint::= and out_of_line_constraint::=)

XMLType_view_clause ::=

Description of XMLType_view_clause.gif follows
Description of the illustration XMLType_view_clause.gif

XMLSchema_spec::=

Description of XMLSchema_spec.gif follows
Description of the illustration XMLSchema_spec.gif

subquery_restriction_clause::=

Description of subquery_restriction_clause.gif follows
Description of the illustration subquery_restriction_clause.gif

Semantics

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, then 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:

FORCE

Specify FORCE if you want to create the view regardless of whether the base tables of the view 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, CREATE VIEW ... FORCE will fail if the base table does not exist or the referenced object type does not exist. CREATE VIEW ... FORCE will also fail if the view definition names 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, then Oracle Database 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 INSTEAD OF triggers, then 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 defining query of the view. The number of aliases must match the number of expressions selected by the view. Aliases must follow the rules for naming Oracle Database schema objects. Aliases must be unique within the view.

If you omit the aliases, the database derives them from the columns or column aliases in the query. For this reason, you must use aliases if the query contains expressions rather than only column names. Also, you must specify aliases if the view definition includes constraints.

Restriction on View Aliases You cannot specify an alias when creating an object view.

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 Database does not enforce view constraints. For a full discussion of view constraints, including restrictions, please refer to "View Constraints".

object_view_clause

The object_view_clause lets you define a view on an object type.

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 as specified in the WITH OBJECT IDENTIFIER clause. If you omit schema, then the database 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 Database defines a system-generated pseudocolumn OBJECT_ID. You can use this column name in queries and to create object views with the WITH OBJECT IDENTIFIER clause.

WITH OBJECT IDENTIFIER Clause

Use the WITH OBJECT IDENTIFIER 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 Object views are subject to the following restrictions:

Note:

The the database8i, Release 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 recommends that you use the new syntax WITH OBJECT IDENTIFIER.

If the object view is defined on an object table or an object view, then you can omit this clause or specify DEFAULT.

DEFAULT Specify DEFAULT if you want the database 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 the database should create the object identifier for the object view.

UNDER Clause

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

Restrictions on Subviews Subviews are subject to the following restrictions:

See Also:

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, then 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 a schema name in the view subquery.

If you create a view with the flashback_query_clause in the defining query, the database does not interpret the AS OF expression at create time but rather each time a user subsequently queries the view.

See Also:

"Creating a Join View: Example" and Oracle Database Application Developer's Guide - Fundamentals for more information on Oracle Flashback Query

Restrictions on the Defining Query of a View The view query is subject to the following restrictions:

The preceding restrictions apply to materialized views as well.

Notes on Updatable Views The following notes apply to 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 INSTEAD OF 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. For a view to be inherently updatable, the following conditions must be met:

A set operator
A DISTINCT operator
An aggregate or analytic function
A GROUP BY, ORDER BY, MODEL, CONNECT BY, or START WITH clause
A collection expression in a SELECT list
A subquery in a SELECT list
A subquery designated WITH READ ONLY
Joins, with some exceptions, as documented in Oracle Database Administrator's Guide

See Also:

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 Database defines a system-generated pseudocolumn OBJECT_ID. You can use this column name in queries and to create object views with the WITH OBJECT IDENTIFIER clause.

See Also:

subquery_restriction_clause

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

WITH READ ONLY Specify WITH READ ONLY to indicate that the table or view cannot be updated.

WITH CHECK OPTION Specify WITH CHECK OPTION to indicate that Oracle Database prohibits any changes to the table or view that would produce rows that are not included in the subquery. When used in the subquery of a DML statement, you can specify this clause in a subquery in the FROM clause but not in subquery in the WHERE clause.

CONSTRAINT constraint Specify the name of the CHECK OPTION constraint. If you omit this identifier, then 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, WITH CHECK OPTION guarantees that inserts and updates result in tables that the defining table subquery can select. For views, WITH CHECK OPTION cannot make this guarantee if:
  • There is a subquery within the defining 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.

Restriction on the subquery_restriction_clause You cannot specify this clause if you have specify an ORDER BY clause.

Examples

Creating a View: Example The following statement creates a view of the sample table employees named emp_view. The view shows the employees in department 20 and their annual salary:

CREATE VIEW emp_view AS 
   SELECT last_name, salary*12 annual_salary
   FROM employees 
   WHERE department_id = 20;

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:

CREATE VIEW emp_sal (emp_id, last_name, 
      email UNIQUE RELY DISABLE NOVALIDATE,
   CONSTRAINT id_pk PRIMARY KEY (emp_id) RELY DISABLE NOVALIDATE)
   AS SELECT employee_id, last_name, email FROM employees;

Creating an Updatable View: Example The following statement creates an updatable view named clerk of all 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 kind of clerk:

CREATE VIEW clerk AS
   SELECT employee_id, last_name, department_id, job_id 
   FROM employees
   WHERE job_id = 'PU_CLERK' 
      or job_id = 'SH_CLERK' 
      or job_id = 'ST_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 WITH CHECK OPTION. 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.

CREATE VIEW clerk AS
   SELECT employee_id, last_name, department_id, job_id 
   FROM employees
   WHERE job_id = 'PU_CLERK' 
      or job_id = 'SH_CLERK' 
      or job_id = 'ST_CLERK'
   WITH CHECK OPTION;

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:

CREATE VIEW locations_view AS
   SELECT d.department_id, d.department_name, l.location_id, l.city
   FROM departments d, locations l
   WHERE d.location_id = l.location_id;

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

COLUMN_NAME                    UPD
------------------------------ ---
DEPARTMENT_ID                  YES
DEPARTMENT_NAME                YES
LOCATION_ID                    NO
CITY                           NO

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.

INSERT INTO locations_view (department_id, department_name)
   VALUES (999, 'Entertainment');

1 row created.

Note:

For you to insert into the table using the view, the view must contain all NOT NULL columns of all tables in the join, unless you have specified DEFAULT values for the NOT NULL columns.

See Also:

Oracle Database Application Developer's Guide - Fundamentals for more information on updating join views

Creating a Read-Only View: Example The following statement creates a read-only view named customer_ro of the oe.customers table. Only the customers' last names, language, and credit limit are visible in this view:

CREATE VIEW customer_ro (name, language, credit)
      AS SELECT cust_last_name, nls_language, credit_limit
      FROM customers
      WITH READ ONLY;

Creating an Object View: Example The following example shows the creation of the type inventory_typ in the oc schema, and the oc_inventories view that is based on that type:

CREATE TYPE inventory_typ
 OID '82A4AF6A4CD4656DE034080020E0EE3D'
 AS OBJECT
    ( product_id          NUMBER(6)
    , warehouse           warehouse_typ
    , quantity_on_hand    NUMBER(8)
    ) ;
/
CREATE OR REPLACE VIEW oc_inventories OF inventory_typ
 WITH OBJECT OID (product_id)
 AS SELECT i.product_id,
           warehouse_typ(w.warehouse_id, w.warehouse_name, w.location_id),
           i.quantity_on_hand
    FROM inventories i, warehouses w
    WHERE i.warehouse_id=w.warehouse_id;

Creating a View on an XMLType Table: Example The following example builds a regular view on the XMLType table xwarehouses, which was created in "Examples":

CREATE VIEW warehouse_view AS
   SELECT VALUE(p) AS warehouse_xml
   FROM xwarehouses p;

You select from such a view as follows:

SELECT e.warehouse_xml.getclobval()
   FROM warehouse_view e
   WHERE EXISTSNODE(warehouse_xml, '//Docks') =1;

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:

CREATE TABLE warehouse_table 
(
  WarehouseID       NUMBER,
  Area              NUMBER,
  Docks             NUMBER,
  DockType          VARCHAR2(100),
  WaterAccess       VARCHAR2(10),
  RailAccess        VARCHAR2(10),
  Parking           VARCHAR2(20),
  VClearance        NUMBER
);

INSERT INTO warehouse_table 
   VALUES(5, 103000,3,'Side Load','false','true','Lot',15);

CREATE VIEW warehouse_view OF XMLTYPE
 XMLSCHEMA "http://www.oracle.com/xwarehouses.xsd" 
    ELEMENT "Warehouse"
    WITH OBJECT ID 
    (extract(OBJECT_VALUE, '/Warehouse/Area/text()').getnumberval())
 AS SELECT XMLELEMENT("Warehouse",
            XMLFOREST(WarehouseID as "Building",
                      area as "Area",
                      docks as "Docks",
                      docktype as "DockType",
                      wateraccess as "WaterAccess",
                      railaccess as "RailAccess",
                      parking as "Parking",
                      VClearance as "VClearance"))
  FROM warehouse_table;

You query this view as follows:

SELECT VALUE(e) FROM warehouse_view e;