7
SQL Statements (continued)

CREATE TRIGGER

Syntax

dml_event_clause::=

referencing_clause::=

Purpose

To create and enable a database trigger. A database trigger is

A stored PL/SQL block associated with a table, a schema, or the database
An anonymous PL/SQL block or a call to a procedure implemented in PL/SQL or Java

Oracle automatically executes a trigger when specified conditions occur. For a description of the various types of triggers, see also Oracle8i Concepts.

For more information on how to design triggers for the above purposes, see Oracle8i Application Developer's Guide - Fundamentals.

Prerequisites

Before a trigger can be created, the user SYS must run the SQL script DBMSSTDX.SQL. The exact name and location of this script depend on your operating system.

To create a trigger in your own schema on a table in your own schema or on your own schema (SCHEMA), you must have the CREATE TRIGGER privilege.
To create a trigger in any schema on a table in any schema, or on another user's schema (schema.SCHEMA), you must have the CREATE ANY TRIGGER privilege.
In addition to the preceding privileges, to create a trigger on DATABASE, you must have the ADMINISTER DATABASE TRIGGER system privilege.

If the trigger issues SQL statements or calls procedures or functions, then the owner of the trigger must have the privileges necessary to perform these operations. These privileges must be granted directly to the owner, rather than acquired through roles.

Keywords and Parameters

Examples

DML Trigger Example

This example creates a BEFORE statement trigger named EMP_PERMIT_CHANGES in the schema SCOTT. You would write such a trigger to place restrictions on DML statements issued on this table (such as when such statements could be issued).

CREATE TRIGGER scott.emp_permit_changes 
    BEFORE 
    DELETE OR INSERT OR UPDATE 
    ON scott.emp 
       pl/sql block

Oracle fires this trigger whenever a DELETE, INSERT, or UPDATE statement affects the EMP table in the schema SCOTT. The trigger EMP_PERMIT_CHANGES is a BEFORE statement trigger, so Oracle fires it once before executing the triggering statement.

DML Trigger Example with Restriction

This example creates a BEFORE row trigger named SALARY_CHECK in the schema SCOTT. The PL/SQL block might specify, for example, that the employee's salary must fall within the established salary range for the employee's job:

CREATE TRIGGER scott.salary_check 
    BEFORE 
    INSERT OR UPDATE OF sal, job ON scott.emp 
    FOR EACH ROW 
    WHEN (new.job <> 'PRESIDENT') 
       pl/sql_block

Oracle fires this trigger whenever one of the following statements is issued:

an INSERT statement that adds rows to the EMP table
an UPDATE statement that changes values of the SAL or JOB columns of the EMP table

SALARY_CHECK is a BEFORE row trigger, so Oracle fires it before changing each row that is updated by the UPDATE statement or before adding each row that is inserted by the INSERT statement.

SALARY_CHECK has a trigger restriction that prevents it from checking the salary of the company president.

Calling a Procedure in a Trigger Body Example

You could create the SALARY_CHECK trigger described in the preceding example by calling a procedure instead of providing the trigger body in a PL/SQL block. Assume you have defined a procedure SCOTT.CHECK_SAL, which verifies that an employee's salary in in an appropriate range. Then you could create the trigger SALARY_CHECK as follows:

CREATE TRIGGER scott.salary_check
   BEFORE INSERT OR UPDATE OF sal, job ON scott.emp
   FOR EACH ROW
   WHEN (new.job<> 'PRESIDENT')
   CALL check_sal(:new.job, :new.sal, :new.ename);

The procedure CHECK_SAL could be implemented in PL/SQL, C, or Java. Also, you can specify :OLD values in the CALL clause instead of :NEW values.

Database Event Trigger Example

This example creates a trigger to log all errors. The PL/SQL block does some special processing for a particular error (invalid logon, error number 1017. This trigger is an AFTER statement trigger, so it is fired after an unsuccessful statement execution (such as unsuccessful logon).

CREATE TRIGGER log_errors AFTER SERVERERROR ON DATABASE 
   BEGIN
      IF (IS_SERVERERROR (1017)) THEN
         <special processing of logon error>
      ELSE
         <log error number>
      END IF;
   END;

DML Trigger Example

This example creates an AFTER statement trigger on any DDL statement CREATE. Such a trigger can be used to audit the creation of new data dictionary objects in your schema.

CREATE TRIOGGER audit_db_object AFTER CREATE
   ON SCHEMA
      pl/sql_block

INSTEAD OF Trigger Example

In this example, customer data is stored in two tables. The object view ALL_CUSTOMERS is created as a UNION of the two tables, CUSTOMERS_SJ and CUSTOMERS_PA. An INSTEAD OF trigger is used to insert values.

CREATE TABLE customers_sj 
  ( cust    NUMBER(6),
    address VARCHAR2(50),
    credit   NUMBER(9,2)  );

CREATE TABLE customers_pa 
  ( cust    NUMBER(6),
    address VARCHAR2(50),
    credit   NUMBER(9,2) );

CREATE TYPE customer_t AS OBJECT
  ( cust    NUMBER(6),
    address   VARCHAR2(50),
    credit    NUMBER(9,2),
    location   VARCHAR2(20)  );

CREATE VIEW all_customers (cust) 
    AS SELECT customer_t (cust, address, credit, 'SAN_JOSE')
    FROM   customers_sj
  UNION ALL
    SELECT customer_t (cust, address, credit, 'PALO_ALTO') 
    FROM   customers_pa;

CREATE TRIGGER instrig INSTEAD OF INSERT ON all_customers 
   FOR EACH ROW 
     BEGIN 
      IF (:new.cust.location = 'SAN_JOSE') THEN 
        INSERT INTO customers_sj 
        VALUES (:new.cust.cust, :new.cust.address,:new.cust.credit); 
      ELSE 
       INSERT INTO customers_pa 
       VALUES (:new.cust.cust, :new.cust.address, :new.cust.credit); 
      END IF; 
    END;

CREATE TYPE

Syntax

create_incomplete_type::=

create_object_type::=

element_list::=

invoker_rights_clause::=

pragma_clause::=

procedure_spec | function_spec::=

call_spec::=

Java_declaration::=

C_declaration::=

create_varray_type::=

create_nested_table_type::=

Purpose

To create an object type, named varying array (varray), nested table type, or an incomplete object type.

Oracle implicitly defines a constructor method for each user-defined type that you create. A constructor is a system-supplied procedure that is used in SQL statements or in PL/SQL code to construct an instance of the type value. The name of the constructor method is the same as the name of the user-defined type.

The parameters of the object type constructor method are the data attributes of the object type. They occur in the same order as the attribute definition order for the object type. The parameters of a nested table or varray constructor are the elements of the nested table or the varray.

An incomplete type is a type created by a forward type definition. It is called "incomplete" because it has a name but no attributes or methods. It can be referenced by other types, and so can be used to define types that refer to each other. However, you must fully specify the type before you can use it to create a table or an object column or a column of a nested table type.

For more information about objects, incomplete types, varrays, and nested tables see the PL/SQL User's Guide and Reference, Oracle8i Application Developer's Guide - Fundamentals, and Oracle8i Concepts.

Prerequisites

To create a type in your own schema, you must have the CREATE TYPE system privilege. To create a type in another user's schema, you must have the CREATE ANY TYPE system privilege. You can acquire these privileges explicitly or be granted them through a role.

The owner of the type must either be explicitly granted the EXECUTE object privilege in order to access all other types referenced within the definition of the type, or the type owner must be granted the EXECUTE ANY TYPE system privilege. The owner cannot obtain these privileges through roles.

If the type owner intends to grant other users access to the type, the owner must be granted the EXECUTE object privilege to the referenced types with the GRANT OPTION or the EXECUTE ANY TYPE system privilege with the ADMIN OPTION. Otherwise, the type owner has insufficient privileges to grant access on the type to other users.

Keywords and Parameters

OR REPLACE

re-creates the type if it already exists. Use this clause to change the definition of an existing type without first dropping it.

Users previously granted privileges on the re-created object type can use and reference the object type without being granted privileges again.
If any function-based indexes depend on the type, Oracle marks the indexes DISABLED.

schema

is the schema to contain the type. If you omit schema, Oracle creates the type in your current schema.

type_name

is the name of an object type, a nested table type, or a varray type.
If creating the type results in compilation errors, Oracle returns an error. You can see the associated compiler error messages with the SQL*Plus command SHOW ERRORS.

create_object_type

creates the type as a user-defined object type. The variables that form the data structure are called attributes. The member subprograms that define the object's behavior are called methods. AS OBJECT is required when creating an object type.

invoker_rights_clause

specifies whether the member functions and procedures of the object type execute with the privileges and in the schema of the user who owns the object type or with the privileges and in the schema of CURRENT_USER. This specification applies to the corresponding type body as well. (For information on how CURRENT_USER is determined, see Oracle8i Concepts and Oracle8i Application Developer's Guide - Fundamentals.)
This clause also determines how Oracle resolves external names in queries, DML operations, and dynamic SQL statements in the member functions and procedures of the type. For more information refer to PL/SQL User's Guide and Reference.
Restriction: You can specify this clause only for an object type, not for a nested table or varray type.

AUTHID CURRENT_USER

specifies that the member functions and procedures of the object type execute with the privileges of CURRENT_USER. This clause creates an "invoker-rights type."
This clause also specifies that external names in queries, DML operations, and dynamic SQL statements resolve in the schema of CURRENT_USER. External names in all other statements resolve in the schema in which the type resides.

AUTHID DEFINER

specifies that the member functions and procedures of the object type execute with the privileges of the owner of the schema in which the functions and procedures reside, and that external names resolve in the schema where the member functions and procedures reside. This is the default.

datatype

is the name of the attribute's Oracle built-in datatype or user-defined type. For a list of possible datatypes, see "Datatypes".
Restrictions:

You cannot specify attributes of type ROWID, LONG, or LONG ROW.

You cannot create an object with NCLOB, NCHAR, or NVARCHAR2 attributes, but you can specify parameters of these datatypes in methods.

You cannot specify a datatype of UROWID for a user-defined object type.

If you specify an object of type REF, the target object must have an object identifier.

attribute

specifies, for an object type, the name of an object attribute. Attributes are data items with a name and a type specifier that form the structure of the object. You must specify at least one attribute for each object type.

MEMBER

specifies a function or procedure subprogram associated with the object type that is referenced as an attribute. Typically you invoke member methods in a "selfish" style, such as object_expression.method(). This class of method has an implicit first argument referenced as SELF in the method's body, which represents the object on which the method has been invoked.

STATIC

also specifies a function or procedure subprogram associated with the object type. However, unlike member methods, static methods do not have any implicit parameters (that is, SELF is not referenceable in their body). They are typically invoked as type_name.method().

For both member and static methods, you must specify a corresponding method body in the object type body for each procedure or function specification. See "CREATE TYPE BODY". For information about method invocation and methods, see PL/SQL User's Guide and Reference.

procedure_spec | function_spec

is the specification of a procedure or function subprogram. The RETURN clause is valid only for a function. The syntax shown is an abbreviated form. For the full syntax with all possible clauses, see "CREATE PROCEDURE" and "CREATE FUNCTION".
If this subprogram does not include the declaration of the procedure or function, you must issue a corresponding CREATE TYPE BODY statement. See "CREATE TYPE BODY".
For a list of restrictions on user-defined functions, see "Restrictions on User-Defined Functions".

call_spec

is the call specification ("call spec") that maps a Java or C method name, parameter types, and return type to their SQL counterparts. If all the member methods in the type have been defined in this clause, you need not issue a corresponding CREATE TYPE BODY statement.

In Java_declaration, 'string' identifies the Java implementation of the method. For more information, see Oracle8i Java Stored Procedures Developer's Guide.

For an explanation of the parameters and semantics of the C_declaration, see Oracle8i Application Developer's Guide - Fundamentals.

pragma_clause

specifies a compiler directive.

PRAGMA RESTRICT_REFERENCES

is a compiler directive that denies member functions read/write access to database tables, packaged variables, or both, and thereby helps to avoid side effects. For more information, see Oracle8i Application Developer's Guide - Fundamentals.

method_name

is the name of the MEMBER function or procedure to which the pragma is being applied.

DEFAULT

specifies that the pragma should be applied to all methods in the type for which a pragma has not been explicitly specified.

WNDS

specifies the constraint writes no database state (does not modify database tables).

WNPS

specifies the constraint writes no package state (does not modify packaged variables).

RNDS

specifies the constraint reads no database state (does not query database tables).

RNPS

specifies the constraint reads no package state (does not reference packages variables).

TRUST

specifies that the restrictions listed in the pragma are not actually to be enforced, but are simply trusted to be true.

MAP MEMBER function_spec

specifies a member function (map method) that returns the relative position of a given instance in the ordering of all instances of the object. A map method is called implicitly and induces an ordering of object instances by mapping them to values of a predefined scalar type. PL/SQL uses the ordering to evaluate Boolean expressions and to perform comparisons.

If the argument to the map method is null, the map method returns null and the method is not invoked.

An object specification can contain only one map method, which must be a function. The result type must be a predefined SQL scalar type, and the map function can have no arguments other than the implicit SELF argument.

Note: If type_name will be referenced in queries involving sorts (through an ORDER BY, GROUP BY, DISTINCT, or UNION clause) or joins, and you want those queries to be parallelized, you must specify a MAP member function.

ORDER MEMBER function_spec

specifies a member function (ORDER method) that takes an instance of an object as an explicit argument and the implicit SELF argument and returns either a negative, zero, or positive integer. The negative, positive, or zero indicates that the implicit SELF argument is less than, equal to, or greater than the explicit argument.

If either argument to the order method is null, the order method returns null and the method is not invoked.
When instances of the same object type definition are compared in an ORDER BY clause, the order method function_specification is invoked.

An object specification can contain only one ORDER method, which must be a function having the return type NUMBER.

You can define either a MAP method or an ORDER method in a type specification, but not both. If you declare either method, you can compare object instances in SQL.

If neither a MAP nor an ORDER method is specified, only comparisons for equality or inequality can be performed. Therefore object instances cannot be ordered. Instances of the same type definition are equal only if each pair of their corresponding attributes is equal. No comparison method needs to be specified to determine the equality of two object types.

Use MAP if you are performing extensive sorting or hash join operations on object instances. MAP is applied once to map the objects to scalar values and then the scalars are used during sorting and merging. A MAP method is more efficient than an ORDER method, which must invoke the method for each object comparison. You must use a MAP method for hash joins. You cannot use an ORDER method because the hash mechanism hashes on the object value. For more information about object value comparisons, see Oracle8i Application Developer's Guide - Fundamentals.

create_varray_type

creates the type as an ordered set of elements, each of which has the same datatype. You must specify a name and a maximum limit of zero or more. The array limit must be an integer literal. Oracle does not support anonymous varrays.

The type name for the objects contained in the varray must be one of the following:

A built-in datatype,

A REF, or

An object type, including an object with varray attributes.

The type name for the objects contained in the varray cannot be

An object type with a nested table attribute,

A varray type, or

A TABLE type.

Restrictions:

A collection type cannot contain any other collection type, either directly or indirectly. That is, a varray type cannot contain any elements that are varrays or nested tables.

You cannot create varray types of LOB datatypes.

create_nested_table_type

creates a named nested table of type datatype.
When datatype is an object type, the nested table type describes a table whose columns match the name and attributes of the object type.
When datatype is a scalar type, then the nested table type describes a table with a single, scalar type column called "column_value".

Restrictions:

A collection type cannot contain any other collection type, either directly or indirectly. That is, a nested table type cannot contain any elements that are varrays or nested tables.

You cannot specify NCLOB for datatype. However, you can specify CLOB or BLOB.

Examples

Object Type Example

The following example creates object type PERSON_T with LOB attributes:

CREATE TYPE person_t AS OBJECT
  (name CHAR(20),
   resume CLOB,
   picture BLOB);

Varray Type Example

The following statement creates MEMBERS_TYPE as a varray type with 100 elements:

CREATE TYPE members_type AS VARRAY(100) OF CHAR(5);

Nested Table Type Example

The following example creates a named table type PROJECT_TABLE of object type PROJECT_T:

CREATE TYPE project_t AS OBJECT 
  (pno CHAR(5), 
   pname CHAR(20), 
   budgets DEC(7,2));

CREATE TYPE project_table AS TABLE OF project_t;

Constructor Example

The following example invokes method constructor COL.GETBAR():

CREATE TYPE foo AS OBJECT (a1 NUMBER,  
                  MEMBER FUNCTION getbar RETURN NUMBER,); 
CREATE TABLE footab(col foo); 

SELECT col.getbar() FROM footab;

Unlike function invocations, method invocations require parentheses, even when the methods do not have additional arguments.

The next example invokes the system-defined constructor to construct the FOO_T object and insert it into the FOO_TAB table:

CREATE TYPE foo_t AS OBJECT (a1 NUMBER, a2 NUMBER);
CREATE TABLE foo_tab (b1 NUMBER, b2 foo_t);
INSERT INTO foo_tab VALUES (1, foo_t(2,3));

For more information about constructors, see Oracle8i Application Developer's Guide - Fundamentals and PL/SQL User's Guide and Reference.

Static Method Example

The following example changes the definition of the EMPLOYEE_T type to associate it with the CONSTRUCT_EMP function:

CREATE OR REPLACE TYPE employee_t AS OBJECT( 
   empid RAW(16), 
   ename CHAR(31), 
   dept   REF department_t, 
   STATIC function construct_emp
      (name VARCHAR2, dept REF department_t) 
   RETURN employee_t
  );

This statement requires the following type body statement:

CREATE OR REPLACE TYPE BODY employee_t IS 
   STATIC FUNCTION construct_emp
      (name varchar2, dept REF department_t) 
   RETURN employee_t IS 
      BEGIN 
         return employee_t(SYS_GUID(),name,dept);  
      END; 
   END;

This type and type body definition allows the following operation:

INSERT INTO emptab 
   VALUES (employee_t.construct_emp('John Smith', NULL));

CREATE TYPE BODY

Syntax

procedure_declaration | function_declaration::=

call_spec::=

Java_declaration::=

C_declaration::=

Purpose

To define or implement the member methods defined in the object type specification. You create object types with the CREATE TYPE and the CREATE TYPE BODY statements. The CREATE TYPE statement specifies the name of the object type, its attributes, methods, and other properties. The CREATE TYPE BODY statement contains the code for the methods in the type.

For each method specified in an object type specification for which you did not specify the call_spec, you must specify a corresponding method body in the object type body.

For information on creating and modifying a type specification, see "CREATE TYPE" and "ALTER TYPE".

Prerequisites

Every member declaration in the CREATE TYPE specification for object types must have a corresponding construct in the CREATE TYPE or CREATE TYPE BODY statement.

To create or replace a type body in your own schema, you must have the CREATE TYPE or the CREATE ANY TYPE system privilege. To create an object type in another user's schema, you must have the CREATE ANY TYPE system privileges. To replace an object type in another user's schema, you must have the DROP ANY TYPE system privileges.

Keywords and Parameters

OR REPLACE

re-creates the type body if it already exists. Use this clause to change the definition of an existing type body without first dropping it.

Users previously granted privileges on the re-created object type body can use and reference the object type body without being granted privileges again.

You can use this clause to add new member subprogram definitions to specifications added with the ALTER TYPE ... REPLACE statement.

schema

is the schema to contain the type body. If you omit schema, Oracle creates the type body in your current schema.

type_name

is the name of an object type.

MEMBER | STATIC

declares or implements a method function or procedure subprogram associated with the object type specification. For a description of the difference between member and static methods, see "CREATE TYPE". For information about overloading subprogram names within a package, see PL/SQL User's Guide and Reference.

You must define a corresponding method name, optional parameter list, and (for functions) a return type in the object type specification for each procedure or function declaration.

procedure_declaration

is the declaration of a procedure subprogram.

function_declaration

is the declaration of a function subprogram.

For more information, see "CREATE PROCEDURE", "CREATE FUNCTION", and Oracle8i Application Developer's Guide - Fundamentals.

MAP MEMBER

declares or implements a member function (MAP method) that returns the relative position of a given instance in the ordering of all instances of the object. A map method is called implicitly and specifies an ordering of object instances by mapping them to values of a predefined scalar type. PL/SQL uses the ordering to evaluate Boolean expressions and to perform comparisons.

If the argument to the map method is null, the map method returns null and the method is not invoked.

An object type body can contain only one map method, which must be a function. The map function can have no arguments other than the implicit SELF argument.

ORDER MEMBER

specifies a member function (ORDER method) that takes an instance of an object as an explicit argument and the implicit SELF argument and returns either a negative, zero, or positive integer. The negative, positive, or zero indicates that the implicit SELF argument is less than, equal to, or greater than the explicit argument.

If either argument to the order method is null, the order method returns null and the method is not invoked.
When instances of the same object type definition are compared in an ORDER BY clause, Oracle invokes the order method function_spec.

An object specification can contain only one ORDER method, which must be a function having the return type NUMBER.

You can declare either a MAP method or an ORDER method, but not both. If you declare either method, you can compare object instances in SQL.

If you do not declare either method, you can compare object instances only for equality or inequality. Instances of the same type definition are equal only if each pair of their corresponding attributes is equal.

procedure_declaration | function_declaration

is the declaration of a procedure or function subprogram. The RETURN clause is valid only for a function. The syntax shown is an abbreviated form. For the full syntax with all possible clauses, see "CREATE PROCEDURE" and "CREATE FUNCTION".

pl/sql_block

declares the procedure or function. For more information, see PL/SQL User's Guide and Reference.

call_spec

is the call specification ("call spec") that maps a Java or C method name, parameter types, and return type to their SQL counterparts.

In Java_declaration, 'string' identifies the Java implementation of the method. For more information, see Oracle8i Java Stored Procedures Developer's Guide.

For an explanation of the parameters and semantics of the C_declaration, see Oracle8i Application Developer's Guide - Fundamentals.

AS EXTERNAL

is an alternative way of declaring a C method. This clause has been deprecated and is supported for backward compatibility only. Oracle Corporation recommends that you use the AS LANGUAGE C syntax.

Examples

The following object type body implements member subprograms for RATIONAL.

CREATE TYPE BODY rational
  IS 
   MAP MEMBER FUNCTION rat_to_real RETURN REAL IS
      BEGIN 
         RETURN numerator/denominator;  
      END; 

   MEMBER PROCEDURE normalize IS
      gcd NUMBER := integer_operations.greatest_common_divisor 
                     (numerator, denominator);
      BEGIN
         numerator := numerator/gcd;
         denominator := denominator/gcd;
      END;

   MEMBER FUNCTION plus(x rational) RETURN rational IS
      r rational := rational_operations.make_rational
                      (numerator*x.denominator + 
                       x.numerator*denominator,
                       denominator*x.denominator);
      BEGIN
         RETURN r;
      END;

   END;

CREATE USER

Syntax

Purpose

To create and configure a database user, or an account through which you can log in to the database and establish the means by which Oracle permits access by the user.

Note:
You can enable a user to connect to Oracle through an proxy (that is, an application or application server). For syntax and discussion, refer to "ALTER USER".

Prerequisites

You must have CREATE USER system privilege.

When you create a user with the CREATE USER statement, the user's privilege domain is empty. To log on to Oracle, a user must have CREATE SESSION system privilege. Therefore, after creating a user, you should grant the user at least the CREATE SESSION privilege. See "GRANT system_privileges_and_roles".

Keywords and Parameters

user

is the name of the user to be created. This name can contain only characters from your database character set and must follow the rules described in the section "Schema Object Naming Rules". Oracle recommends that the user name contain at least one single-byte character regardless of whether the database character set also contains multi-byte characters.

IDENTIFIED

indicates how Oracle authenticates the user. See Oracle8i Application Developer's Guide - Fundamentals and your operating system specific documentation for more information.

BY password

creates a local user and indicates that the user must specify password to log on. Passwords can contain only single-byte characters from your database character set regardless of whether this character set also contains multibyte characters.
Passwords must follow the rules described in the section "Schema Object Naming Rules", unless you are using Oracle's password complexity verification routine. That routine requires a more complex combination of characters than the normal naming rules permit. You implement this routine with the UTLPWDMG.SQL script, which is further described in Oracle8i Administrator's Guide.
Also refer to Oracle8i Administrator's Guide for a detailed description and explanation of how to use password management and protection.

EXTERNALLY

creates an external user and indicates that a user must be authenticated by an external service (such as an operating system or a third-party service). Doing so causes Oracle to rely on the login authentication of the operating system to ensure that a specific operating system user has access to a specific database user.

WARNING: Oracle strongly recommends that you do not use IDENTIFIED EXTERNALLY with operating systems that have inherently weak login security. For more information, see Oracle8i Administrator's Guide.

GLOBALLY AS 'external_name'

creates a global user and indicates that a user must be authenticated by the enterprise directory service. The 'external_name' string is the X.509 name at the enterprise directory service that identifies this user. It should be of the form 'CN=username,other_attributes', where other_attributes is the rest of the user's distinguished name (DN) in the directory.

Note: You can control the ability of an application server to connect as the specified user and to activate that user's roles using the ALTER USER statement. See "ALTER USER"

DEFAULT TABLESPACE

identifies the default tablespace for objects that the user creates. If you omit this clause, objects default to the SYSTEM tablespace. For more information on tablespaces, see "CREATE TABLESPACE".

TEMPORARY TABLESPACE

identifies the tablespace for the user's temporary segments. If you omit this clause, temporary segments default to the SYSTEM tablespace.

QUOTA

allows the user to allocate space in the tablespace and optionally establishes a quota of integer bytes. Use K or M to specify the quota in kilobytes or megabytes. This quota is the maximum space in the tablespace the user can allocate.

A CREATE USER statement can have multiple QUOTA clauses for multiple tablespaces.

UNLIMITED

allows the user to allocate space in the tablespace without bound.

PROFILE

reassigns the profile named to the user. The profile limits the amount of database resources the user can use. If you omit this clause, Oracle assigns the DEFAULT profile to the user. See also "GRANT system_privileges_and_roles" and "CREATE PROFILE".

DEFAULT ROLE

lets you assign and enable a default role or roles to the user.

role assigns one or more predefined roles

ALL [EXCEPT] role assigns all predefined roles to the user, or all except those specified.

NONE assigns no roles to the user.

PASSWORD EXPIRE

causes the user's password to expire. This setting forces the user (or the DBA) to change the password before the user can log in to the database.

ACCOUNT LOCK

locks the user's account and disables access.

ACCOUNT UNLOCK

unlocks the user's account and enables access to the account.

Examples

If you create a new user with PASSWORD EXPIRE, the user's password must be changed before attempting to log in to the database. You can create the user SIDNEY by issuing the following statement:

CREATE USER sidney 
    IDENTIFIED BY welcome 
    DEFAULT TABLESPACE cases_ts 
    QUOTA 10M ON cases_ts 
    QUOTA 5M ON temp_ts 
    QUOTA 5M ON system 
    PROFILE engineer 
    PASSWORD EXPIRE;

The user SIDNEY has the following characteristics:

The password WELCOME
Default tablespace CASES_TS, with a quota of 10 megabytes
Temporary tablespace TEMP_TS, with a quota of 5 megabytes
Access to the tablespace SYSTEM, with a quota of 5 megabytes
Limits on database resources defined by the profile ENGINEER
An expired password, which must be changed before SIDNEY can log in to the database

To create a user accessible only by the operating system account GEORGE, prefix GEORGE by the value of the initialization parameter OS_AUTHENT_PREFIX. For example, if this value is "OPS$", you can create the user OPS$GEORGE with the following statement:

CREATE USER ops$george 
    IDENTIFIED EXTERNALLY 
    DEFAULT TABLESPACE accs_ts 
    TEMPORARY TABLESPACE temp_ts 
    QUOTA UNLIMITED ON accs_ts
    QUOTA UNLIMITED ON temp_ts;

The user OPS$GEORGE has the following additional characteristics:

Default tablespace ACCS_TS
Default temporary tablespace TEMP_TS
Unlimited space on the tablespaces ACCS_TS and TEMP_TS
Limits on database resources defined by the DEFAULT profile

The following example creates user CINDY as a global user:

CREATE USER cindy 
   IDENTIFIED GLOBALLY AS 'CN=cindy,OU=division1,O=oracle,C=US'
   DEFAULT TABLESPACE legal_ts
   QUOTA 20M ON legal_ts
   PROFILE lawyer;

CREATE VIEW

Syntax

subquery: See "SELECT and Subqueries".

with_clause::=

Purpose

To define a view, 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.

For information on various types of views and their uses, see Oracle8i Concepts, Oracle8i Application Developer's Guide - Fundamentals, and Oracle8i Administrator's Guide.

For information on modifying a view, see "ALTER VIEW". For information on removing a view from the database, see "DROP VIEW".

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. For information on these privileges, see "SELECT and Subqueries", "INSERT", "UPDATE", and "DELETE". 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.

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 (see Oracle8i Administrator's Guide).

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. For more information on the shortcomings of partition reviews, see Oracle8i Concepts.

Oracle recommends that you use partitioned tables rather than partition views in most operational environments. For more information about partitioned tables, see "CREATE TABLE".

Keywords and Parameters

OR REPLACE

re-creates 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. See "CREATE TRIGGER" for more information about the INSTEAD OF clause.

Note: If any materialized views are dependent on view, those materialized views will be marked INVALID and 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. For information on refreshing invalid materialized views, see "ALTER MATERIALIZED VIEW / SNAPSHOT". For information on materialized views in general, see Oracle8i Concepts.

FORCE

creates 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

creates 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

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

view

is 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

specifies 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 schema objects in the section, "Referring to Schema Objects and Parts". 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.

OF type_name

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. For more information about creating objects, see "CREATE TYPE".

WITH OBJECT IDENTIFIER

specifies 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

specifies that the intrinsic object identifier of the underlying object table or object view will be used to uniquely identify each row.

attribute

is an attribute of the object type from which the object identifier for the object view is to be created.

AS subquery

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:

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.

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

For more information on updatable views, see Oracle8i Administrator's Guide. For more information about updating object views or relational views that support object types, see Oracle8i Application Developer's Guide - Fundamentals.

with_clause

restricts the subquery in one of the following ways:

WITH READ ONLY

specifies that no delete, inserts, or updates can be performed through the view.

WITH CHECK OPTION

specifies that inserts and updates performed through the view must 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

assigns 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 EMP table 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 CLERKS 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 subquery contains a join. If at least one column in the subquery 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. For more information on updating join views, see the Oracle8i Application Developer's Guide - Fundamentals.

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.

Read-Only View Example

The following statement creates a read-only view named CLERKS of all clerks in the EMP table. Only the employee's 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;

DELETE

Syntax

table_expression_clause::=

subquery: See "SELECT and Subqueries".

with_clause::=

table_collection_expression::=

where_clause::=

returning_clause::=

Purpose

To remove rows from a table, a partitioned table, a view's base table, or a view's partitioned base table.

Prerequisites

For you to delete rows from a table, the table must be in your own schema or you must have DELETE privilege on the table.

For you to delete rows from the base table of a view, the owner of the schema containing the view must have DELETE privilege on the base table. Also, if the view is in a schema other than your own, you must be granted DELETE privilege on the view.

The DELETE ANY TABLE system privilege also allows you to delete rows from any table or table partition, or any view's base table.

If the SQL92_SECURITY initialization parameter is set to TRUE, then you must have SELECT privilege on the table to perform a DELETE that references table columns (such as the columns in a where_clause).

Keywords and Parameters

hint

is a comment that passes instructions to the optimizer on choosing an execution plan for the statement. For the syntax and description of hints, see "Hints" and Oracle8i Tuning.

table_expression_clause

schema

is the schema containing the table or view. If you omit schema, Oracle assumes the table or view is in your own schema.

table | view | subquery

is the name of a table or view, or the column or columns resulting from a subquery, from which the rows are to be deleted. If you specify view, Oracle deletes rows from the view's base table.
If table (or the base table of view) contains one or more domain index columns, this statements executes the appropriate indextype delete routine. For more information on these routines, see Oracle8i Data Cartridge Developer's Guide.

Issuing a DELETE statement against a table fires any DELETE triggers defined on the table.
All table or index space released by the deleted rows is retained by the table and index.

Restrictions:

You cannot execute this statement if table (or the base table of view) contains any domain indexes marked LOADING or FAILED.

You cannot specify the sample_clause in a DELETE statement.

You cannot specify the ORDER BY clause in the subquery of the table_expression_clause.

You cannot delete from a view except through INSTEAD OF triggers if the view's defining query contains one of the following constructs:

- A set operator

- A DISTINCT operator

- An aggregate 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 you specify an index, index partition, or index subpartition that has been marked UNUSABLE, the DELETE statement will fail unless the SKIP_UNUSABLE_INDEXES parameter has been set to TRUE. For more information, see "ALTER SESSION".

PARTITION (partition_name) | SUBPARTITION (subpartition_name)

specifies that partition_name or subpartition_name is the name of the partition or subpartition within table targeted for deletes.
You need not specify the partition name when deleting values from a partitioned table. However, in some cases specifying the partition name is more efficient than a complicated where_clause.

dblink

is the complete or partial name of a database link to a remote database where the table or view is located. For information on referring to database links, see "Referring to Objects in Remote Databases". You can delete rows from a remote table or view only if you are using Oracle's distributed functionality.

If you omit dblink, Oracle assumes that the table or view is located on the local database.

with_clause

restricts the subquery in one of the following ways:

WITH READ ONLY specifies that the subquery cannot be updated.

WITH CHECK OPTION specifies that Oracle prohibits any changes to that table that would produce rows that are not included in the subquery. See the WITH CHECK OPTION Example .

table_collection_expression

informs Oracle that the collection value expression should be treated as a table. You can use a table_collection_expression to delete only those rows that also exist in another table.

collection_expression

is a subquery that selects a nested table column from table or view.

Note: In earlier releases of Oracle, table_collection_expression was expressed as "THE subquery". That usage is now deprecated.

where_clause

deletes only rows that satisfy the condition. The condition can reference the table and can contain a subquery. See the syntax description in "Conditions". You can delete rows from a remote table or view only if you are using Oracle's distributed functionality.

If you omit dblink, Oracle assumes that the table or view is located on the local database.
If you omit the where_clause, Oracle deletes all rows of the table or view.

t_alias

provides a correlation name for the table, view, subquery, or collection value to be referenced elsewhere in the statement. Table aliases are generally used in DELETE statements with correlated queries.

Note: This alias is required if the table_expression_clause references any object type attributes or object type methods.

returning_clause

retrieves the rows affected by the DELETE statement.

You can use a returning_clause to return values from deleted columns, and thereby eliminate the need to issue a SELECT statement following the DELETE statement.

When deleting a single row, a DELETE statement with a returning_clause can retrieve column expressions using the deleted row, rowid, and REFs to the deleted row and store them in PL/SQL variables or bind variables.

When deleting multiple rows, a DELETE statement with the returning_clause stores values from expressions, rowids, and REFs involving the deleted rows in bind arrays.

You can also use DELETE with a returning_clause to delete from views with single base tables.
For host binds, the datatype and size of the expression must be compatible with the bind variable.

expr

is any of the syntax descriptions in "Expressions". You must specify a column expression in the returning_clause for each variable in the data_item list.

INTO

indicates that the values of the changed rows are to be stored in the variable(s) specified in data_item list.

data_item

is a PL/SQL variable or bind variable that stores the retrieved expr value.

Restrictions:

You cannot use this clause with parallel DML or with remote objects.

You cannot retrieve LONG types with this clause.

Examples

Basic Examples

The following statement deletes all rows from a table named TEMP_ASSIGN.

DELETE FROM temp_assign;

The following statement deletes from the EMP table all sales staff who made less than $100 commission last month:

DELETE FROM emp
    WHERE JOB = 'SALESMAN'
    AND COMM < 100;

The following statement has the same effect as the preceding example, but uses a subquery:

DELETE FROM (select * from emp)
    WHERE JOB = 'SALESMAN'
    AND COMM < 100;

Remote Database Example

The following statement deletes all rows from the bank account table owned by the user BLAKE on a database accessible by the database link DALLAS:

DELETE FROM blake.accounts@dallas;

Nested Table Example

The following example deletes rows of nested table PROJS where the department number is either 123 or 456, or the department's budget is greater than 456.78:

DELETE THE(SELECT projs
           FROM dept d WHERE d.dno = 123) AS p
   WHERE p.pno IN (123, 456) OR p.budgets > 456.78;

Partition Example

The following example removes rows from partition NOV98 of the SALES table:

DELETE FROM sales PARTITION (nov98)
   WHERE amount_of_sale != 0;

Example

The following example returns column SAL from the deleted rows and stores the result in bind array :1:

DELETE FROM emp
   WHERE job = 'SALESMAN' AND COMM < 100
   RETURNING sal INTO :1;

DISASSOCIATE STATISTICS

Syntax

Purpose

To disassociate a statistics type (or default statistics) from columns, standalone functions, packages, types, domain indexes, or indextypes.

For more information on statistics type associations, see "ASSOCIATE STATISTICS".

Prerequisites

To issue this statement, you must have the appropriate privileges to alter the base object (table, function, package, type, domain index, or indextype).

Keywords and Parameters

FROM COLUMNS | FUNCTIONS | PACKAGES | TYPES | INDEXES | INDEXTYPES

specifies a list of columns, standalone functions, packages, types, domain indexes, or indextypes from which you are disassociating statistics.
If you do not specify schema, Oracle assumes the object is in your own schema.
If you have collected user-defined statistics on the object, the statement fails unless you specify FORCE.

FORCE

deletes the association regardless of whether any statistics exist for the object using the statistics type. If statistics do exist, the statistics are deleted before the association is deleted.

Note: When you drop an object with which a statistics type has been associated, Oracle automatically disassociates the statistics type with the FORCE option and drops all statistics that have been collected with the statistics type.

Example

This statement disassociates statistics from the PACK package in the HR schema:

DISASSOCIATE STATISTICS FROM PACKAGES hr.pack;

DROP CLUSTER

Syntax

Purpose

To remove a cluster from the database.

You cannot uncluster an individual table. Instead you must

Create a new table with the same structure and contents as the old one, but with no CLUSTER clause,
Drop the old table,
Use the RENAME statement to give the new table the name of the old one, and
Grant privileges on the new unclustered table, as grants on the old clustered table do not apply.

See "CREATE TABLE", "DROP TABLE", "RENAME", "GRANT system_privileges_and_roles".

Prerequisites

The cluster must be in your own schema or you must have the DROP ANY CLUSTER system privilege.

Keywords and Parameters

schema

is the schema containing the cluster. If you omit schema, Oracle assumes the cluster is in your own schema.

cluster

is the name of the cluster to be dropped. Dropping a cluster also drops the cluster index and returns all cluster space, including data blocks for the index, to the appropriate tablespace(s).

INCLUDING TABLES

drops all tables that belong to the cluster.

CASCADE CONSTRAINTS

drops all referential integrity constraints from tables outside the cluster that refer to primary and unique keys in tables of the cluster. If you omit this clause and such referential integrity constraints exist, Oracle returns an error and does not drop the cluster.

Example

This statement drops a cluster named GEOGRAPHY, all its tables, and any referential integrity constraints that refer to primary or unique keys in those tables:

DROP CLUSTER geography 
    INCLUDING TABLES 
    CASCADE CONSTRAINTS;

DROP CONTEXT

Syntax

Purpose

To remove a context namespace from the database. For more information on contexts, see "CREATE CONTEXT" and Oracle8i Concepts.

Note:
Removing a context namespace does not invalidate any context under that namespace that has been set for a user session. However, the context will be invalid the next time the user attempts to set that context.

Prerequisites

You must have the DROP ANY CONTEXT system privilege.

Keywords and Parameters

namespace

is the name of the context namespace to drop. You cannot drop the build-in namespace USERENV.

Click the "Next" button to see the next statement in this chapter.

CREATE	creates a new trigger. When you create a trigger, Oracle enables it automatically. You can subsequently disable and enable a trigger with the DISABLE and ENABLE clause of the ALTER TRIGGER or ALTER TABLE statement. For information on how to enable and disable triggers, see "ALTER TRIGGER" and "ALTER TABLE".
	If a trigger produces compilation errors, it is still created, but it fails on execution. You can see the associated compiler error messages with the SQL*Plus command SHOW ERRORS. This means it effectively blocks all triggering DML statements until it is disabled, replaced by a version without compilation errors, or dropped.
OR REPLACE	re-creates the trigger if it already exists. Use this clause to change the definition of an existing trigger without first dropping it.
schema	is the schema to contain the trigger. If you omit schema, Oracle creates the trigger in your own schema.
trigger	is the name of the trigger to be created.
BEFORE	causes Oracle to fire the trigger before executing the triggering event. For row triggers, this is a separate firing before each affected row is changed. Restrictions: You cannot specify a BEFORE trigger on a view or an object view. When defining a BEFORE trigger for LOB columns, you can read the :OLD value but not the :NEW value. You cannot write either the :OLD or the :NEW value.
AFTER	causes Oracle to fire the trigger after executing the triggering event. For row triggers, this is a separate firing after each affected row is changed. Restrictions: You cannot specify an AFTER trigger on a view or an object view. When defining an AFTER trigger for LOB columns, you can read the :OLD value but not the :NEW value. You cannot write either the :OLD or the :NEW value.
	Note: When you create a snapshot log for a table, Oracle implicitly creates an AFTER ROW trigger on the table. This trigger inserts a row into the snapshot log whenever an INSERT, UPDATE, or DELETE statement modifies the table's data. You cannot control the order in which multiple row triggers fire. Therefore, you should not write triggers intended to affect the content of the snapshot. For more information on snapshot logs, see CREATE MATERIALIZED VIEW LOG / SNAPSHOT LOG .

INSTEAD OF	causes Oracle to fire the trigger instead of executing the triggering event. By default, INSTEAD OF triggers are activated for each row. If a view is inherently updatable and has INSTEAD OF triggers, the triggers take preference. In other words, Oracle fires the triggers instead of performing DML on the view.
	Restrictions: INSTEAD OF is a valid clause only for views. You cannot specify an INSTEAD OF trigger on a table. If a view has INSTEAD OF triggers, any views created on it must have INSTEAD OF triggers, even if the views are inherently updatable. When defining INSTEAD OF TRIGGERS for LOB columns, you can read both the :OLD and the :NEW value, but you cannot write either the :OLD or the :NEW values.
Note: You can create multiple triggers of the same type (BEFORE, AFTER, or INSTEAD OF) that fire for the same statement on the same table. The order in which Oracle fires these triggers is indeterminate. If your application requires that one trigger be fired before another of the same type for the same statement, combine these triggers into a single trigger whose trigger action performs the trigger actions of the original triggers in the appropriate order.
*dml_event_clause*	specifies one of three DML statements that can cause the trigger to fire. Oracle fires the trigger in the existing user transaction.
	DELETE	causes Oracle to fire the trigger whenever a DELETE statement removes a row from the table or an element from a nested table.
	INSERT	causes Oracle to fire the trigger whenever an INSERT statement adds a row to table or an element to a nested table.
	UPDATE	causes Oracle to fire the trigger whenever an UPDATE statement changes a value in one of the columns specified after OF. If you omit OF, Oracle fires the trigger whenever an UPDATE statement changes a value in any column of the table or nested table. For an UPDATE trigger, you can specify object type, varray, and REF columns after OF to indicate that the trigger should be fired whenever an UPDATE statement changes a value in one of the columns. However, you cannot change the values of these columns in the body of the trigger itself.
		Note: Using OCI functions or the DBMS_LOB package to update LOB values or LOB attributes of object columns does not cause Oracle to fire triggers defined on the table containing the columns or the attributes.

		Restrictions: You cannot specify OF with UPDATE for an INSTEAD OF trigger. Oracle fires INSTEAD OF triggers whenever an UPDATE changes a value in any column of the view. You cannot specify nested table or LOB columns with OF. See AS subquery of "CREATE VIEW" for a list of constructs that prevent inserts, updates, or deletes on a view.
	Performing DML operations directly on nested table columns does not cause Oracle to fire triggers defined on the table containing the nested table column
ddl_event	is one of three DDL statements that can cause the trigger to fire. You can create triggers for these events on DATABASE or SCHEMA unless otherwise noted. You can create BEFORE and AFTER triggers for these events. Oracle fires the trigger in the existing user transaction.
	CREATE	causes Oracle to fire the trigger whenever a CREATE statement adds a new database object to the data dictionary.
	ALTER	causes Oracle to fire the trigger whenever an ALTER statement modifies a database object in the data dictionary.
	DROP	causes Oracle to fire the trigger whenever a DROP statement removes a database object from the data dictionary.
	Restriction: DDL triggers are supported only for the following database objects: cluster, function, index, package, procedure, role, sequence, synonym, table, tablespace, trigger, type, view, and user.
database_event	describes a particular state of the database that can cause the trigger to fire. You can create triggers for these events on DATABASE or SCHEMA unless otherwise noted. For each of these triggering events, Oracle opens an autonomous transaction scope, fires the trigger, and commits any separate transaction (regardless of any existing user transaction). For more information on autonomous transaction scope, see PL/SQL User's Guide and Reference.
	SERVERERROR	causes Oracle to fire the trigger whenever a server error message is logged.
	LOGON	causes Oracle to fire the trigger whenever a client application logs onto the database.
	LOGOFF	causes Oracle to fire the trigger whenever a client applications logs off the database.
	STARTUP	causes Oracle to fire the trigger whenever the database is opened.
	SHUTDOWN	causes Oracle to fire the trigger whenever an instance of the database is shut down.
	Notes: Only AFTER triggers are relevant for LOGON, STARTUP, and SERVERERROR. Only BEFORE triggers are relevant for LOGOFF and SHUTDOWN. AFTER STARTUP and BEFORE SHUTDOWN triggers apply only to DATABASE.
ON	determines the database object on which the trigger is to be created.
	[schema.] table \| view	specifies the schema and table or view name of the of one of the following on which the trigger is to be created: table or view object table or object view a column of nested-table type
		If you omit schema, Oracle assumes the table is in your own schema. You can create triggers on index-organized tables. Restriction: You cannot create a trigger on a table in the schema SYS.
	NESTED TABLE	specifies that the trigger is being defined on column nested_table_column of a view. Such a trigger will fire only if the DML operates on the elements of the nested table. Restriction: You can specify NESTED TABLE only for INSTEAD OF triggers.
	DATABASE	specifies that the trigger is being defined on the entire database.
	SCHEMA	specifies that the trigger is being defined on the current schema.
*referencing_clause*	specifies correlation names. You can use correlation names in the PL/SQL block and WHEN condition of a row trigger to refer specifically to old and new values of the current row. The default correlation names are OLD and NEW. If your row trigger is associated with a table named OLD or NEW, use this clause to specify different correlation names to avoid confusion between the table name and the correlation name.
	If the trigger is defined on a nested table, OLD and NEW refer to the row of the nested table, and PARENT refers to the current row of the parent table. If the trigger is defined on an object table or view, OLD and NEW refer to object instances. Restriction: This clause is valid only for DML event triggers (not DDL or database event triggers).
FOR EACH ROW	designates the trigger to be a row trigger. Oracle fires a row trigger once for each row that is affected by the triggering statement and meets the optional trigger constraint defined in the WHEN condition. Note: This clause is applies only to DML events, not to DDL or database events.
	Except for INSTEAD OF triggers, if you omit this clause, the trigger is a statement trigger. Oracle fires a statement trigger only once when the triggering statement is issued if the optional trigger constraint is met. INSTEAD OF trigger statements are implicitly activated for each row.
WHEN (condition)	specifies the trigger restriction, which is a SQL condition that must be satisfied for Oracle to fire the trigger. See the syntax description of condition in "Conditions". This condition must contain correlation names and cannot contain a query.
	Restrictions: You can specify a trigger restriction only for a row trigger. Oracle evaluates this condition for each row affected by the triggering statement. You cannot specify trigger restrictions for INSTEAD OF trigger statements. You can reference object columns or their attributes, or varray, nested table, or LOB columns. You cannot invoke PL/SQL functions or methods in the trigger restriction.
pl/sql_block	is the PL/SQL block that Oracle executes to fire the trigger. For information on PL/SQL, including how to write PL/SQL blocks, see PL/SQL User's Guide and Reference.
	The PL/SQL block of a database trigger can contain one of a series of built-in functions in the SYS schema designed solely to extract system event attributes. These functions can be used only in the PL/SQL block of a database trigger. For information on these functions, see Oracle8i Application Developer's Guide - Fundamentals.
	Restrictions: The PL/SQL block of a trigger cannot contain transaction control SQL statements (COMMIT, ROLLBACK, SAVEPOINT, and SET CONSTRAINT) if the block is executed within the same transaction. For more information, see Oracle8i Application Developer's Guide - Fundamentals. You can reference and use LOB columns in the trigger action inside the PL/SQL block, but you cannot modify their values within the trigger action.
call_procedure_statement	enables you to call a stored procedure, rather than specifying inline the trigger code as a PL/SQL block. The syntax of this statement is the same as that for "CALL", with the following exceptions:
	You cannot specify the INTO clause of CALL, because it applies only to functions. You cannot specify bind variables in expr. To reference columns of tables on which the trigger is being defined, you must specify :NEW and :OLD. See the "Calling a Procedure in a Trigger Body Example".

OR REPLACE	re-creates the type if it already exists. Use this clause to change the definition of an existing type without first dropping it.
	Users previously granted privileges on the re-created object type can use and reference the object type without being granted privileges again. If any function-based indexes depend on the type, Oracle marks the indexes DISABLED.
schema	is the schema to contain the type. If you omit schema, Oracle creates the type in your current schema.
type_name	is the name of an object type, a nested table type, or a varray type. If creating the type results in compilation errors, Oracle returns an error. You can see the associated compiler error messages with the SQL*Plus command SHOW ERRORS.
*create_object_type*	creates the type as a user-defined object type. The variables that form the data structure are called attributes. The member subprograms that define the object's behavior are called methods. AS OBJECT is required when creating an object type.
*invoker_rights_clause*	specifies whether the member functions and procedures of the object type execute with the privileges and in the schema of the user who owns the object type or with the privileges and in the schema of CURRENT_USER. This specification applies to the corresponding type body as well. (For information on how CURRENT_USER is determined, see Oracle8i Concepts and Oracle8i Application Developer's Guide - Fundamentals.) This clause also determines how Oracle resolves external names in queries, DML operations, and dynamic SQL statements in the member functions and procedures of the type. For more information refer to PL/SQL User's Guide and Reference. Restriction: You can specify this clause only for an object type, not for a nested table or varray type.
	AUTHID CURRENT_USER	specifies that the member functions and procedures of the object type execute with the privileges of CURRENT_USER. This clause creates an "invoker-rights type." This clause also specifies that external names in queries, DML operations, and dynamic SQL statements resolve in the schema of CURRENT_USER. External names in all other statements resolve in the schema in which the type resides.
	AUTHID DEFINER	specifies that the member functions and procedures of the object type execute with the privileges of the owner of the schema in which the functions and procedures reside, and that external names resolve in the schema where the member functions and procedures reside. This is the default.
datatype	is the name of the attribute's Oracle built-in datatype or user-defined type. For a list of possible datatypes, see "Datatypes". Restrictions: You cannot specify attributes of type ROWID, LONG, or LONG ROW. You cannot create an object with NCLOB, NCHAR, or NVARCHAR2 attributes, but you can specify parameters of these datatypes in methods. You cannot specify a datatype of UROWID for a user-defined object type. If you specify an object of type REF, the target object must have an object identifier.
attribute	specifies, for an object type, the name of an object attribute. Attributes are data items with a name and a type specifier that form the structure of the object. You must specify at least one attribute for each object type.
MEMBER	specifies a function or procedure subprogram associated with the object type that is referenced as an attribute. Typically you invoke member methods in a "selfish" style, such as `object_expression.method()`. This class of method has an implicit first argument referenced as SELF in the method's body, which represents the object on which the method has been invoked.
STATIC	also specifies a function or procedure subprogram associated with the object type. However, unlike member methods, static methods do not have any implicit parameters (that is, SELF is not referenceable in their body). They are typically invoked as `type_name.method()`.
For both member and static methods, you must specify a corresponding method body in the object type body for each procedure or function specification. See "CREATE TYPE BODY". For information about method invocation and methods, see PL/SQL User's Guide and Reference.
procedure_spec \| function_spec	is the specification of a procedure or function subprogram. The RETURN clause is valid only for a function. The syntax shown is an abbreviated form. For the full syntax with all possible clauses, see "CREATE PROCEDURE" and "CREATE FUNCTION". If this subprogram does not include the declaration of the procedure or function, you must issue a corresponding CREATE TYPE BODY statement. See "CREATE TYPE BODY". For a list of restrictions on user-defined functions, see "Restrictions on User-Defined Functions".
call_spec	is the call specification ("call spec") that maps a Java or C method name, parameter types, and return type to their SQL counterparts. If all the member methods in the type have been defined in this clause, you need not issue a corresponding CREATE TYPE BODY statement.
	In Java_declaration, 'string' identifies the Java implementation of the method. For more information, see Oracle8i Java Stored Procedures Developer's Guide. For an explanation of the parameters and semantics of the C_declaration, see Oracle8i Application Developer's Guide - Fundamentals.
*pragma_clause*	specifies a compiler directive.
PRAGMA RESTRICT_REFERENCES	is a compiler directive that denies member functions read/write access to database tables, packaged variables, or both, and thereby helps to avoid side effects. For more information, see Oracle8i Application Developer's Guide - Fundamentals.
	method_name	is the name of the MEMBER function or procedure to which the pragma is being applied.
	DEFAULT	specifies that the pragma should be applied to all methods in the type for which a pragma has not been explicitly specified.
	WNDS	specifies the constraint writes no database state (does not modify database tables).
	WNPS	specifies the constraint writes no package state (does not modify packaged variables).
	RNDS	specifies the constraint reads no database state (does not query database tables).
	RNPS	specifies the constraint reads no package state (does not reference packages variables).
	TRUST	specifies that the restrictions listed in the pragma are not actually to be enforced, but are simply trusted to be true.
MAP MEMBER function_spec	specifies a member function (map method) that returns the relative position of a given instance in the ordering of all instances of the object. A map method is called implicitly and induces an ordering of object instances by mapping them to values of a predefined scalar type. PL/SQL uses the ordering to evaluate Boolean expressions and to perform comparisons.
	If the argument to the map method is null, the map method returns null and the method is not invoked.
	An object specification can contain only one map method, which must be a function. The result type must be a predefined SQL scalar type, and the map function can have no arguments other than the implicit SELF argument.
	Note: If type_name will be referenced in queries involving sorts (through an ORDER BY, GROUP BY, DISTINCT, or UNION clause) or joins, and you want those queries to be parallelized, you must specify a MAP member function.
ORDER MEMBER function_spec	specifies a member function (ORDER method) that takes an instance of an object as an explicit argument and the implicit SELF argument and returns either a negative, zero, or positive integer. The negative, positive, or zero indicates that the implicit SELF argument is less than, equal to, or greater than the explicit argument.
	If either argument to the order method is null, the order method returns null and the method is not invoked. When instances of the same object type definition are compared in an ORDER BY clause, the order method function_specification is invoked.
	An object specification can contain only one ORDER method, which must be a function having the return type NUMBER.
You can define either a MAP method or an ORDER method in a type specification, but not both. If you declare either method, you can compare object instances in SQL.
If neither a MAP nor an ORDER method is specified, only comparisons for equality or inequality can be performed. Therefore object instances cannot be ordered. Instances of the same type definition are equal only if each pair of their corresponding attributes is equal. No comparison method needs to be specified to determine the equality of two object types.
Use MAP if you are performing extensive sorting or hash join operations on object instances. MAP is applied once to map the objects to scalar values and then the scalars are used during sorting and merging. A MAP method is more efficient than an ORDER method, which must invoke the method for each object comparison. You must use a MAP method for hash joins. You cannot use an ORDER method because the hash mechanism hashes on the object value. For more information about object value comparisons, see Oracle8i Application Developer's Guide - Fundamentals.
*create_varray_type*	creates the type as an ordered set of elements, each of which has the same datatype. You must specify a name and a maximum limit of zero or more. The array limit must be an integer literal. Oracle does not support anonymous varrays.
	The type name for the objects contained in the varray must be one of the following: A built-in datatype, A REF, or An object type, including an object with varray attributes.
	The type name for the objects contained in the varray cannot be An object type with a nested table attribute, A varray type, or A TABLE type.
	Restrictions: A collection type cannot contain any other collection type, either directly or indirectly. That is, a varray type cannot contain any elements that are varrays or nested tables. You cannot create varray types of LOB datatypes.
*create_nested_table_type*	creates a named nested table of type datatype. When datatype is an object type, the nested table type describes a table whose columns match the name and attributes of the object type. When datatype is a scalar type, then the nested table type describes a table with a single, scalar type column called "column_value".
	Restrictions: A collection type cannot contain any other collection type, either directly or indirectly. That is, a nested table type cannot contain any elements that are varrays or nested tables. You cannot specify NCLOB for datatype. However, you can specify CLOB or BLOB.

OR REPLACE	re-creates the type body if it already exists. Use this clause to change the definition of an existing type body without first dropping it.
	Users previously granted privileges on the re-created object type body can use and reference the object type body without being granted privileges again.
	You can use this clause to add new member subprogram definitions to specifications added with the ALTER TYPE ... REPLACE statement.
schema	is the schema to contain the type body. If you omit schema, Oracle creates the type body in your current schema.
type_name	is the name of an object type.
MEMBER \| STATIC	declares or implements a method function or procedure subprogram associated with the object type specification. For a description of the difference between member and static methods, see "CREATE TYPE". For information about overloading subprogram names within a package, see PL/SQL User's Guide and Reference.
	You must define a corresponding method name, optional parameter list, and (for functions) a return type in the object type specification for each procedure or function declaration.
	procedure_declaration	is the declaration of a procedure subprogram.
	function_declaration	is the declaration of a function subprogram.
	For more information, see "CREATE PROCEDURE", "CREATE FUNCTION", and Oracle8i Application Developer's Guide - Fundamentals.
MAP MEMBER	declares or implements a member function (MAP method) that returns the relative position of a given instance in the ordering of all instances of the object. A map method is called implicitly and specifies an ordering of object instances by mapping them to values of a predefined scalar type. PL/SQL uses the ordering to evaluate Boolean expressions and to perform comparisons.
	If the argument to the map method is null, the map method returns null and the method is not invoked.
	An object type body can contain only one map method, which must be a function. The map function can have no arguments other than the implicit SELF argument.
ORDER MEMBER	specifies a member function (ORDER method) that takes an instance of an object as an explicit argument and the implicit SELF argument and returns either a negative, zero, or positive integer. The negative, positive, or zero indicates that the implicit SELF argument is less than, equal to, or greater than the explicit argument.
	If either argument to the order method is null, the order method returns null and the method is not invoked. When instances of the same object type definition are compared in an ORDER BY clause, Oracle invokes the order method function_spec.
	An object specification can contain only one ORDER method, which must be a function having the return type NUMBER.
You can declare either a MAP method or an ORDER method, but not both. If you declare either method, you can compare object instances in SQL.
If you do not declare either method, you can compare object instances only for equality or inequality. Instances of the same type definition are equal only if each pair of their corresponding attributes is equal.
procedure_declaration \| function_declaration	is the declaration of a procedure or function subprogram. The RETURN clause is valid only for a function. The syntax shown is an abbreviated form. For the full syntax with all possible clauses, see "CREATE PROCEDURE" and "CREATE FUNCTION".
	pl/sql_block	declares the procedure or function. For more information, see PL/SQL User's Guide and Reference.
	call_spec	is the call specification ("call spec") that maps a Java or C method name, parameter types, and return type to their SQL counterparts. In Java_declaration, 'string' identifies the Java implementation of the method. For more information, see Oracle8i Java Stored Procedures Developer's Guide. For an explanation of the parameters and semantics of the C_declaration, see Oracle8i Application Developer's Guide - Fundamentals.
	AS EXTERNAL	is an alternative way of declaring a C method. This clause has been deprecated and is supported for backward compatibility only. Oracle Corporation recommends that you use the AS LANGUAGE C syntax.

user	is the name of the user to be created. This name can contain only characters from your database character set and must follow the rules described in the section "Schema Object Naming Rules". Oracle recommends that the user name contain at least one single-byte character regardless of whether the database character set also contains multi-byte characters.
IDENTIFIED	indicates how Oracle authenticates the user. See Oracle8i Application Developer's Guide - Fundamentals and your operating system specific documentation for more information.
	BY password	creates a local user and indicates that the user must specify password to log on. Passwords can contain only single-byte characters from your database character set regardless of whether this character set also contains multibyte characters. Passwords must follow the rules described in the section "Schema Object Naming Rules", unless you are using Oracle's password complexity verification routine. That routine requires a more complex combination of characters than the normal naming rules permit. You implement this routine with the UTLPWDMG.SQL script, which is further described in Oracle8i Administrator's Guide. Also refer to Oracle8i Administrator's Guide for a detailed description and explanation of how to use password management and protection.
	EXTERNALLY	creates an external user and indicates that a user must be authenticated by an external service (such as an operating system or a third-party service). Doing so causes Oracle to rely on the login authentication of the operating system to ensure that a specific operating system user has access to a specific database user.
		WARNING: Oracle strongly recommends that you do not use IDENTIFIED EXTERNALLY with operating systems that have inherently weak login security. For more information, see Oracle8i Administrator's Guide.
	GLOBALLY AS 'external_name'	creates a global user and indicates that a user must be authenticated by the enterprise directory service. The 'external_name' string is the X.509 name at the enterprise directory service that identifies this user. It should be of the form 'CN=username,other_attributes', where other_attributes is the rest of the user's distinguished name (DN) in the directory.
	Note: You can control the ability of an application server to connect as the specified user and to activate that user's roles using the ALTER USER statement. See "ALTER USER"
DEFAULT TABLESPACE	identifies the default tablespace for objects that the user creates. If you omit this clause, objects default to the SYSTEM tablespace. For more information on tablespaces, see "CREATE TABLESPACE".
TEMPORARY TABLESPACE	identifies the tablespace for the user's temporary segments. If you omit this clause, temporary segments default to the SYSTEM tablespace.
QUOTA	allows the user to allocate space in the tablespace and optionally establishes a quota of integer bytes. Use K or M to specify the quota in kilobytes or megabytes. This quota is the maximum space in the tablespace the user can allocate.
	A CREATE USER statement can have multiple QUOTA clauses for multiple tablespaces.
	UNLIMITED	allows the user to allocate space in the tablespace without bound.
PROFILE	reassigns the profile named to the user. The profile limits the amount of database resources the user can use. If you omit this clause, Oracle assigns the DEFAULT profile to the user. See also "GRANT system_privileges_and_roles" and "CREATE PROFILE".
DEFAULT ROLE	lets you assign and enable a default role or roles to the user. role assigns one or more predefined roles ALL [EXCEPT] role assigns all predefined roles to the user, or all except those specified. NONE assigns no roles to the user.
PASSWORD EXPIRE	causes the user's password to expire. This setting forces the user (or the DBA) to change the password before the user can log in to the database.
ACCOUNT LOCK	locks the user's account and disables access.
ACCOUNT UNLOCK	unlocks the user's account and enables access to the account.

OR REPLACE	re-creates 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. See "CREATE TRIGGER" for more information about the INSTEAD OF clause.
	Note: If any materialized views are dependent on view, those materialized views will be marked INVALID and 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. For information on refreshing invalid materialized views, see "ALTER MATERIALIZED VIEW / SNAPSHOT". For information on materialized views in general, see Oracle8i Concepts.
FORCE	creates 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	creates 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	is the schema to contain the view. If you omit schema, Oracle creates the view in your own schema.
view	is 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	specifies 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 schema objects in the section, "Referring to Schema Objects and Parts". 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.
OF type_name	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. For more information about creating objects, see "CREATE TYPE".
WITH OBJECT IDENTIFIER	specifies 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	specifies that the intrinsic object identifier of the underlying object table or object view will be used to uniquely identify each row.
attribute	is an attribute of the object type from which the object identifier for the object view is to be created.
AS subquery	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: 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. 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 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.
	For more information on updatable views, see Oracle8i Administrator's Guide. For more information about updating object views or relational views that support object types, see Oracle8i Application Developer's Guide - Fundamentals.
*with_clause*	restricts the subquery in one of the following ways:
	WITH READ ONLY	specifies that no delete, inserts, or updates can be performed through the view.
	WITH CHECK OPTION	specifies that inserts and updates performed through the view must 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	assigns 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.

hint	is a comment that passes instructions to the optimizer on choosing an execution plan for the statement. For the syntax and description of hints, see "Hints" and Oracle8i Tuning.
*table_expression_clause*
schema	is the schema containing the table or view. If you omit schema, Oracle assumes the table or view is in your own schema.
table \| view \| subquery	is the name of a table or view, or the column or columns resulting from a subquery, from which the rows are to be deleted. If you specify view, Oracle deletes rows from the view's base table. If table (or the base table of view) contains one or more domain index columns, this statements executes the appropriate indextype delete routine. For more information on these routines, see Oracle8i Data Cartridge Developer's Guide.
	Issuing a DELETE statement against a table fires any DELETE triggers defined on the table. All table or index space released by the deleted rows is retained by the table and index.
	Restrictions: You cannot execute this statement if table (or the base table of view) contains any domain indexes marked LOADING or FAILED. You cannot specify the sample_clause in a DELETE statement. You cannot specify the ORDER BY clause in the subquery of the table_expression_clause. You cannot delete from a view except through INSTEAD OF triggers if the view's defining query contains one of the following constructs: - A set operator - A DISTINCT operator - An aggregate 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 you specify an index, index partition, or index subpartition that has been marked UNUSABLE, the DELETE statement will fail unless the SKIP_UNUSABLE_INDEXES parameter has been set to TRUE. For more information, see "ALTER SESSION".
PARTITION (partition_name) \| SUBPARTITION (subpartition_name)	specifies that partition_name or subpartition_name is the name of the partition or subpartition within table targeted for deletes. You need not specify the partition name when deleting values from a partitioned table. However, in some cases specifying the partition name is more efficient than a complicated where_clause.
dblink	is the complete or partial name of a database link to a remote database where the table or view is located. For information on referring to database links, see "Referring to Objects in Remote Databases". You can delete rows from a remote table or view only if you are using Oracle's distributed functionality.
	If you omit dblink, Oracle assumes that the table or view is located on the local database.
*with_clause*	restricts the subquery in one of the following ways:
	WITH READ ONLY specifies that the subquery cannot be updated.
	WITH CHECK OPTION specifies that Oracle prohibits any changes to that table that would produce rows that are not included in the subquery. See the WITH CHECK OPTION Example .
*table_collection_expression*	informs Oracle that the collection value expression should be treated as a table. You can use a table_collection_expression to delete only those rows that also exist in another table.
	collection_expression	is a subquery that selects a nested table column from table or view.
	Note: In earlier releases of Oracle, table_collection_expression was expressed as "THE subquery". That usage is now deprecated.
*where_clause*	deletes only rows that satisfy the condition. The condition can reference the table and can contain a subquery. See the syntax description in "Conditions". You can delete rows from a remote table or view only if you are using Oracle's distributed functionality.
	If you omit dblink, Oracle assumes that the table or view is located on the local database. If you omit the where_clause, Oracle deletes all rows of the table or view.
t_alias	provides a correlation name for the table, view, subquery, or collection value to be referenced elsewhere in the statement. Table aliases are generally used in DELETE statements with correlated queries.
	Note: This alias is required if the table_expression_clause references any object type attributes or object type methods.
*returning_clause*	retrieves the rows affected by the DELETE statement.
	You can use a returning_clause to return values from deleted columns, and thereby eliminate the need to issue a SELECT statement following the DELETE statement. When deleting a single row, a DELETE statement with a returning_clause can retrieve column expressions using the deleted row, rowid, and REFs to the deleted row and store them in PL/SQL variables or bind variables. When deleting multiple rows, a DELETE statement with the returning_clause stores values from expressions, rowids, and REFs involving the deleted rows in bind arrays. You can also use DELETE with a returning_clause to delete from views with single base tables. For host binds, the datatype and size of the expression must be compatible with the bind variable.
	expr	is any of the syntax descriptions in "Expressions". You must specify a column expression in the returning_clause for each variable in the data_item list.
	INTO	indicates that the values of the changed rows are to be stored in the variable(s) specified in data_item list.
	data_item	is a PL/SQL variable or bind variable that stores the retrieved expr value.
	Restrictions: You cannot use this clause with parallel DML or with remote objects. You cannot retrieve LONG types with this clause.

schema	is the schema containing the cluster. If you omit schema, Oracle assumes the cluster is in your own schema.
cluster	is the name of the cluster to be dropped. Dropping a cluster also drops the cluster index and returns all cluster space, including data blocks for the index, to the appropriate tablespace(s).
INCLUDING TABLES	drops all tables that belong to the cluster.
CASCADE CONSTRAINTS	drops all referential integrity constraints from tables outside the cluster that refer to primary and unique keys in tables of the cluster. If you omit this clause and such referential integrity constraints exist, Oracle returns an error and does not drop the cluster.

7SQL Statements (continued)

CREATE TRIGGER

Syntax

Purpose

Prerequisites

Keywords and Parameters

Examples

DML Trigger Example

DML Trigger Example with Restriction

Calling a Procedure in a Trigger Body Example

Database Event Trigger Example

DML Trigger Example

INSTEAD OF Trigger Example

CREATE TYPE

Syntax

Purpose

Prerequisites

Keywords and Parameters

Examples

Object Type Example

Varray Type Example

Nested Table Type Example

Constructor Example

Static Method Example

CREATE TYPE BODY

Syntax

Purpose

Prerequisites

Keywords and Parameters

Examples

CREATE USER

Syntax

Purpose

Prerequisites

Keywords and Parameters

Examples

CREATE VIEW

Syntax

Purpose

Prerequisites

Partition Views

Keywords and Parameters

Examples

Basic View Example

Updatable View Example

Join View Example

Read-Only View Example

Object View Example

DELETE

Syntax

Purpose

Prerequisites

Keywords and Parameters

Examples

Basic Examples

Remote Database Example

Nested Table Example

Partition Example

Example

DISASSOCIATE STATISTICS

Syntax

Purpose

Prerequisites

Keywords and Parameters

Example

DROP CLUSTER

Syntax

Purpose

Prerequisites

Keywords and Parameters

Example

DROP CONTEXT

Syntax

Purpose

Prerequisites

Keywords and Parameters

7
SQL Statements (continued)