---

CREATE TRIGGER

Purpose

To create and enable a database trigger. A database trigger is a stored PL/SQL block associated with a table. Oracle automatically executes a trigger when a specified SQL statement is issued against the table. See also "Using Triggers".

Note:   Descriptions of commands and clauses preceded by  are available only if the Oracle objects option is installed on your database server.

   

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 issue this statement, you must have one of the following system privileges:

CREATE TRIGGER	lets you create a trigger in your own schema on a table in your own schema.
CREATE ANY TRIGGER	lets you create a trigger in any user's schema on a table in any schema.

 

If the trigger issues SQL statements or calls procedures or functions, then the owner of the schema to contain 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.

Syntax

 

Keywords and Parameters

OR REPLACE	re-creates the trigger if it already exists. Use this option 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. See also"Conditional Predicates", "Parts of a Trigger", and "Types of Triggers".
BEFORE	causes Oracle to fire the trigger before executing the triggering statement. For row triggers, this is a separate firing before each affected row is changed.
	You cannot specify a BEFORE trigger on a view or an object view.
AFTER	causes Oracle to fire the trigger after executing the triggering statement. For row triggers, this is a separate firing after each affected row is changed. See also "Snapshot Log Triggers".
	You cannot specify an AFTER trigger on a view or an object view.
INSTEAD OF	causes Oracle to fire the trigger instead of executing the triggering statement. By default, INSTEAD OF triggers are activated for each row. See also "INSTEAD OF Triggers".
	INSTEAD OF is a valid option only for views. You cannot specify an INSTEAD OF trigger on a table.
DELETE	causes Oracle to fire the trigger whenever a DELETE statement removes a row from the table.
INSERT	causes Oracle to fire the trigger whenever an INSERT statement adds a row to table.
UPDATE	causes Oracle to fire the trigger whenever an UPDATE statement changes a value in one of the columns specified in the OF clause. If you omit the OF clause, Oracle fires the trigger whenever an UPDATE statement changes a value in any column of the table.
	You cannot specify an OF clause with 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 in the OF clause. See also "User-Defined Types, LOB, and REF Columns".
ON	specifies the schema and table or view name of the of one of the following on which the trigger is to be created:  table  object table  view  object view  If you omit schema, Oracle assumes the table is in your own schema. You can create triggers on index-organized tables. You cannot create a trigger on a table in the schema SYS. See also "User-Defined Types, LOB, and REF Columns".
table	is the name of a table or an object table.
view	is the name of a view or an object view.
REFERENCING	specifies correlation names. You can use correlation names in the PL/SQL block and WHEN clause 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 an object table or view, OLD and NEW refer to object instances.
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 clause.   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--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.   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, 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.
	Note: The PL/SQL block of a trigger cannot contain transaction control SQL statements (COMMIT, ROLLBACK, SAVEPOINT, and SET CONSTRAINT).

 

Using Triggers

Oracle automatically fires, or executes, a trigger when a triggering statement is issued. You can use triggers for the following purposes:

• to provide sophisticated auditing and transparent event logging
• to automatically generate derived column values
• to enforce complex security authorizations and business constraints
• to maintain replicate asynchronous tables

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

An existing trigger must be in one of the following states:

• If a trigger is enabled, Oracle fires the trigger whenever a triggering statement is issued and the condition of the trigger restriction is met.
• If a trigger is disabled, Oracle does not fire the trigger when a triggering statement is issued and the condition of the trigger restriction is met.

When you create a trigger, Oracle enables it automatically. You can subsequently disable and enable a trigger with the DISABLE and ENABLE options of the ALTER TRIGGER command or the ALTER TABLE command.

For information on how to enable and disable triggers, see ALTER TRIGGER, ALTER TABLE, the ENABLE clause, and the DISABLE clause.

Before Release 7.3, Oracle parsed and compiled a trigger whenever it was fired. From Release 7.3 onward, Oracle stores a compiled version of a trigger in the data dictionary and calls this compiled version when the trigger is fired. This feature provides a significant performance improvement for applications that use many triggers.

If a trigger produces compilation errors, it is still created, but it fails on execution. This means it effectively blocks all triggering DML statements until it is disabled, replaced by a version without compilation errors, or dropped.

To embed a CREATE TRIGGER statement inside an Oracle precompiler program, you must terminate the statement with the keyword END-EXEC followed by the embedded SQL statement terminator for the specific language.

Conditional Predicates

When you create a trigger for more than one DML operation, you can use conditional predicates within the trigger body to execute specific blocks of code, depending on the type of statement that fires the trigger. Conditional predicates are evaluated as follows:

INSERTING	returns true if the trigger fires for an INSERT statement.
DELETING	returns true if the trigger fires for a DELETE statement.
UPDATING	returns true if the trigger fires for an UPDATE statement.
UPDATING (column_name)	returns true if the trigger fires for an UPDATE statement and column_name is updated.   Note: You cannot specify an object attribute as column_name.

 

For more information about creating and using conditional predicates in trigger bodies, see Oracle8 Application Developer's Guide.

Example

The following example uses conditional predicates to provide information about which DML statement fires trigger AUDIT_TRIGGER:

CREATE TRIGGER audit_trigger BEFORE INSERT OR DELETE OR UPDATE
  ON classified_table FOR EACH ROW
  BEGIN
    IF INSERTING THEN
       INSERT INTO audit_table
          VALUES (USER || ' is inserting' ||
                    ' new key: ' || :new.key);
    ELSIF DELETING THEN
       INSERT INTO audit_table
          VALUES (USER || ' is deleting' ||
                    ' old key: ' || :old.key);
    ELSIF UPDATING('FORMULA') THEN
       INSERT INTO audit_table
          VALUES (USER || ' is updating' ||
                    ' old formula: ' || :old.formula ||
                    ' new formula: ' || :new.formula);
    ELSIF UPDATING THEN
       INSERT INTO audit_table
          VALUES (USER || ' is updating' ||
                    ' old key: ' || :old.key ||
                    ' new key: ' || :new.key);
    END IF;
  END;

Parts of a Trigger

The syntax of the CREATE TRIGGER statement includes the following parts of the trigger:

Triggering statement

The definition of the triggering statement specifies what SQL statements cause Oracle to fire the trigger.

DELETE  INSERT  UPDATE	You must specify at least one of these commands that causes Oracle to fire the trigger. You can specify as many as three.
ON	You must also specify the table with which the trigger is associated. The triggering statement is one that modifies this table. You can define a trigger on an index-organized table.

 

Trigger restriction

The trigger restriction specifies an additional condition that must be satisfied for a row trigger to be fired. You specify this condition with the WHEN clause. This condition must be a SQL condition, rather than a PL/SQL condition.

Trigger action

The trigger action specifies the PL/SQL block Oracle executes to fire the trigger.

Oracle evaluates the condition of the trigger restriction whenever a triggering statement is issued. If this condition is satisfied, then Oracle fires the trigger using the trigger action.

Types of Triggers

You can create different types of triggers. The type of a trigger determines:

• when Oracle fires the trigger in relation to executing the triggering statement
• how many times Oracle fires the trigger

The type of a trigger depends on the BEFORE, AFTER, and FOR EACH ROW options of the CREATE TRIGGER command. Using all combinations of these options for the above parts, you can create four types of triggers. Table 4-9 describes each type of trigger, its properties, and the options used to create it.

Table 4-9 Types of Triggers

	FOR EACH option
	STATEMENT (or omitted)	ROW
BEFORE Option	BEFORE statement trigger: Oracle fires the trigger once before executing the triggering statement.	BEFORE row trigger: Oracle fires the trigger before modifying each row affected by the triggering statement.
AFTER Option	AFTER statement trigger: Oracle fires the trigger once after executing the triggering statement.	AFTER row trigger: Oracle fires the trigger after modifying each row affected by the triggering statement.

 

For a single table, you can create each type of trigger for each of the following commands:

• DELETE
• INSERT
• UPDATE

You can also create triggers that fire for more than one command.

If you create multiple triggers of the same type that fire for the same command 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 command, combine these triggers into a single trigger whose trigger action performs the trigger actions of the original triggers in the appropriate order.

Snapshot Log Triggers

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

Example I

This example creates a BEFORE statement trigger named EMP_PERMIT_CHANGES in the schema SCOTT. This trigger ensures that changes to employee records are made only during business hours on working days:

CREATE TRIGGER scott.emp_permit_changes 
    BEFORE 
    DELETE OR INSERT OR UPDATE 
    ON scott.emp 
    DECLARE 
        dummy  INTEGER; 
    BEGIN 
        /* If today is a Saturday or Sunday, 
           then return an error.*/ 
        IF (TO_CHAR(SYSDATE, 'DY') = 'SAT' OR 
         TO_CHAR(SYSDATE, 'DY') = 'SUN') 
         THEN raise_application_error( -20501, 
       'May not change employee table during the weekend'); 
        END IF; 
       /* Compare today's date with the dates of all 
          company holidays. If today is a company holiday, 
           then return an error.*/ 
        SELECT COUNT(*) 
         INTO dummy 
          FROM company_holidays 
          WHERE day = TRUNC(SYSDATE); 
        IF dummy > 0 
         THEN raise_application_error( -20501, 
          'May not change employee table during a holiday'); 
        END IF; 
        /*If the current time is before 8:00AM or after
          6:00PM, then return an error.    
        */ 
        IF (TO_CHAR(SYSDATE, 'HH24') < 8 OR 
          TO_CHAR(SYSDATE, 'HH24') >= 18) 
          THEN raise_application_error( -20502, 
       'May only change employee table during working hours'); 
        END IF; 
      END; 

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.

The trigger performs the following operations:

1. If the current day is a Saturday or Sunday, the trigger raises an application error with a message that the employee table cannot be changed during weekends.
2. The trigger compares the current date with the dates listed in the table of company holidays.
3. If the current date is a company holiday, the trigger raises an application error with a message that the employee table cannot be changed during holidays.
4. If the current time is not between 8:00AM and 6:00PM, the trigger raises an application error with a message that the employee table can be changed only during business hours.

Example II

This example creates a BEFORE row trigger named SALARY_CHECK in the schema SCOTT. Whenever a new employee is added to the employee table or an existing employee's salary or job is changed, this trigger guarantees that the employee's salary falls 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') 
    DECLARE 
       minsal NUMBER; 
       maxsal NUMBER; 
    BEGIN 
        /* Get the minimum and maximum salaries for the
          employee's job from the SAL_GUIDE table.  */ 
        SELECT minsal, maxsal 
         INTO minsal, maxsal 
         FROM sal_guide 
         WHERE job = :new.job; 
         /* If the employee's salary is below the minimum or  */ 
         /* above the maximum for the job, then generate an   */ 
         /* error.*/ 
        IF (:new.sal < minsal OR :new.sal > maxsal) 
        THEN raise_application_error( -20601, 
          'Salary ' || :new.sal || ' out of range for job ' 
          || :new.job || ' for employee ' || :new.ename ); 
        END IF; 
    END; 

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. For each new or modified employee row that meets this condition, the trigger performs the following steps:

1. The trigger queries the salary guide table for the minimum and maximum salaries for the employee's job.
2. The trigger compares the employee's salary with these minimum and maximum values.
3. If the employee's salary does not fall within the acceptable range, the trigger raises an application error with a message that the employee's salary is not within the established range for the employee's job.

INSTEAD OF Triggers

Use INSTEAD OF triggers to perform DELETE, UPDATE, or INSERT operations on views, which are not inherently modifiable. "The View Query" for a list of constructs that prevent inserts, updates, or deletes on a view. In the following 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.location = 'SAN_JOSE') THEN 
           INSERT INTO customers_sj 
            VALUES (:new.cust, :new.address, :new.credit); 
        ELSE 
           INSERT INTO customers_pa 
            VALUES (:new.cust, :new.address, :new.credit); 
        END IF; 
       END;

User-Defined Types, LOB, and REF Columns

You can reference and use object, VARRAY, nested table, LOB, and REF columns in the trigger action inside the PL/SQL block, but you cannot modify their values within the trigger action. For an UPDATE trigger, object type, VARRAY type, and REF type, you can specify columns in the OF clause to indicate that the trigger should be fired whenever an UPDATE statement changes a value in one of the columns.

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. When defining any other triggers for LOB columns, you can read the :OLD value but not the :NEW value; you cannot write either the :OLD or the :NEW value.

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. Likewise, performing DML operations directly on nested table columns does not cause Oracle to fire triggers defined on the table containing the nested table column.

Related Topics

CREATE TRIGGER

DROP TRIGGER

ENABLE clause

DISABLE clause

CREATE VIEW

ALTER VIEW

---

CREATE TYPE

Purpose

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

Note:   This command is available only if the Oracle objects option is installed on your database server.

   

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. However, it can be referenced by other types, and so can be used to define types that refer to each other. See also "Incomplete Object Types".

For more information about objects, incomplete types, VARRAYs, and nested tables see the PL/SQL User's Guide and Reference, Oracle8 Application Developer's Guide, and Oracle8 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.

Syntax

create_incomplete_type::=

create_varray_type::=

create_nested_table_type::=

create_object_type::=

pragma_clause::=

datatype::=
 

Keywords and Parameters

OR REPLACE	re-creates the type if it already exists. Use this option 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.
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.
AS OBJECT	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. See also "Constructors".
			AS TABLE OF	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".
	Note that a collection type cannot contain any other collection type, either directly or indirectly.
AS VARRAY(limit)	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. Only variable-length arrays are supported. Oracle does not support anonymous VARRAYs.
	The type name for the objects contained in the VARRAY must be one of the following:  a scalar datatype (see description below). The available datatypes are listed in the syntax for this command.  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.
	Note that a collection type cannot contain any other collection type, either directly or indirectly.
OF datatype	is the name of any Oracle built-in datatype or library type. ROWID, LONG, and LONG RAW are not valid datatypes. For a list of possible datatypes, see the syntax definition for CREATE TYPE.
REF object_type_name	associates an instance of a source type with an instance of the target object. A REF logically identifies and locates the target object. The target object must have an object identifier.
attribute_name	is an object attribute name. Attributes are data items with a name and a type specifier that form the structure of the object.
MEMBER	specifies a function or procedure subprogram associated with the object type which is referenced as an attribute. For information about overloading subprogram names within a package, see the PL/SQL User's Guide and Reference.
	You must specify a corresponding method body in the object type body for each procedure or function specification. See CREATE TYPE BODY.
procedure_specification	is the specification of a procedure subprogram.
function_specification	is the specification of a function subprogram.
MAP MEMBER function_specification	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.
	A scalar value is always manipulated as a single unit. Scalars are mapped directly to the underlying hardware. An integer, for example, occupies 4 or 8 contiguous bytes of storage, in memory or on disk.
	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.
ORDER MEMBER function_specification	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.
	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 INTEGER.
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 and thus object instances cannot be ordered. Note that 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. For more information about object value comparisons, "Object Values" and Oracle8 Application Developer's Guide.
pragma_clause:
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 the PL/SQL User's Guide and Reference.
	method_name	is the name of the MEMBER function or procedure to which the pragma is being applied.
	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).

 

Incomplete Object Types

You must fully specify an incomplete object type before you can use it to create a table or an object column or a column of a nested table type.

You cannot create nested VARRAY or nested table types. That is, VARRAY and nested table types cannot contain any elements that are VARRAYs or nested tables. You cannot create VARRAY types of LOB datatypes.

Example I

The following example creates object type PERSON_T with LOB attributes:

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

Example II

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

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

Example III

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;

Example IV

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

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

SELECT col.getbar() FROM footab;

Constructors

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.

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

Example

This 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 Oracle8 Application Developer's Guide and PL/SQL User's Guide and Reference.

Related Topics

ALTER TYPE

CREATE TYPE BODY

Oracle8 Application Developer's Guide

PL/SQL User's Guide and Reference

---

CREATE TYPE BODY

Purpose

To define or implement the member methods defined in the object type specification. See also "TYPE and TYPE BODY".

Note:   This command is available only if the Oracle objects option is installed on your database server.

   

Prerequisites

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

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

Syntax

 

Keywords and Parameters

OR REPLACE	re-creates the type body if it already exists. Use this option 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 option to add new member subprogram definitions to specifications added with the ALTER TYPE ... REPLACE command.
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	declares or implements a method function or procedure subprogram associated with the object type specification. 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. See CREATE TYPE BODY.
procedure_declaration	is the declaration of a procedure subprogram. For more information about writing type bodies, see PL/SQL User's Guide and Reference.
function_declaration	is the declaration of a function subprogram. For more information about writing type bodies, see PL/SQL User's Guide and Reference.
MAP MEMBER function_declaration	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.
	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 function_specification	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.
	When instances of the same object type definition are compared in an ORDER BY clause, Oracle invokes the order method function_specification.
	An object specification can contain only one ORDER method, which must be a function having the return type INTEGER.
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 only object instances for equality or inequality. Note that instances of the same type definition are equal only if each pair of their corresponding attributes is equal.

 

TYPE and TYPE BODY

You create object types with the CREATE TYPE and the CREATE TYPE BODY commands. The CREATE TYPE command specifies the name of the object type, its attributes, methods, and other properties. The CREATE TYPE BODY command contains the code for the methods in the type.

For each method specified in an object type specification, there must be a corresponding method body in the object type body.

Example

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 INTEGER := 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;

Related Topics

CREATE TYPE

ALTER TYPE

PL/SQL User's Guide and Reference

---

CREATE USER

Purpose

To create 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. You can assign the following optional properties to the user:

• default tablespace
• temporary tablespace
• quotas for allocating space in tablespaces
• profile containing resource limits

For a detailed description and explanation of how to use password management and protection, see Oracle8 Administrator's Guide.

Prerequisites

You must have CREATE USER system privilege.

Syntax

 

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 Oracle8 Application Developer's Guide and your operating system specific documentation for more information.
	BY password	requires the user to specify this password to log on. Password must follow the rules described in the section "Schema Object Naming Rules" and can only contain single-byte characters from your database character set regardless of whether this character set also contains multibyte characters.
	EXTERNALLY	indicates that a user must be authenticated by an external service (such as an operating system or a third-party service). See also "Verifying Users Through Your Operating System".
	GLOBALLY AS 'external_name'	indicates that a user must be authenticated by the Oracle Security Service. The 'external_name' string is the X.509 name at the Oracle Security Service that identifies this user. See also "Verifying Users Through the Network".
DEFAULT TABLESPACE	identifies the default tablespace for objects that the user creates. If you omit this clause, objects default to the SYSTEM 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. This quota is the maximum space in the tablespace the user can allocate. You can also use K or M to specify the quota in kilobytes or megabytes. See also "Establishing Tablespace Quotas for Users".
	Note that a CREATE USER command 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 "Granting Privileges to a User".
PASSWORD EXPIRE	causes the user's password to expire. Change the password before attempting to 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.

 

Verifying Users Through Your Operating System

Using CREATE USER ... IDENTIFIED EXTERNALLY enables a database administrator to create a database user that can only be accessed from a specific operating system account. Effectively, you are relying on the login authentication of the operating system to ensure that a specific operating system user has access to a specific database user. Thus, the effective security of such database accounts is entirely dependent on the strength of that security mechanism. Oracle strongly recommends that you do not use IDENTIFIED EXTERNALLY with operating systems that have inherently weak login security. For more information, see Oracle8 Administrator's Guide.

Verifying Users Through the Network

Using CREATE USER ... IDENTIFIED GLOBALLY enables a database administrator to create a database user that can only be authorized by an external authentication service, such as Oracle Security Server (OSS), or any external authentication system. For more information about OSS, see The Oracle Security Server Guide and Oracle8 Distributed Database Systems.

Establishing Tablespace Quotas for Users

To create an object or a temporary segment, the user must allocate space in some tablespace. To allow the user to allocate space, use the QUOTA clause. A CREATE USER statement can have multiple QUOTA clauses, each for a different tablespace. Other clauses can appear only once.

Note that you yourself need not have a quota on a tablespace to establish a quota for another user on that tablespace.

Granting Privileges to a User

For a user to perform any database operation, the user's privilege domain must contain a privilege that authorizes that operation. A user's privilege domain contains all privileges granted to the user and all privileges in the privilege domains of the user's enabled roles.

Notes:

• When you create a user with the CREATE USER command, the user's privilege domain is empty.
• To log on to Oracle, a user must have CREATE SESSION system privilege. After creating a user, you should grant the user this privilege.

Example I

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 carton 
    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 CARTON
• 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 attempting to log in to the database

Example II

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

Example III

The following example creates user CINDY as a global user:

CREATE USER cindy IDENTIFIED GLOBALLY AS 'CN=cindyuser'
    DEFAULT TABLESPACE legal_ts
    QUOTA 20M ON legal_ts
    PROFILE lawyer;

Related Topics

ALTER USER

CREATE PROFILE

CREATE TABLESPACE

GRANT (System Privileges and Roles)

---

CREATE VIEW

Purpose

To define a view, a logical table based on one or more tables or views.

Note:   Descriptions of commands and clauses preceded by  are available only if the Oracle objects option is installed on your database server.

   

Use CREATE VIEW to 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 more information about creating and using object views, see "Using Views" and Oracle8 Application Developer's Guide. For examples of creating views, see "Examples".

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, 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 EXECUTE object privileges on that object type.

Syntax

 

subquery: See "Subqueries"

Keywords and Parameters

OR REPLACE	re-creates the view if it already exists. You can use this option to change the definition of an existing view without dropping, re-creating, and regranting object privileges previously granted on it.
	Note that 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 option.
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. Note that 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.
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.
	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 OID clause. See also "Object Views".
	If you omit schema, Oracle creates the object view in your own schema. For more information about creating objects, see CREATE TYPE.
[ WITH OBJECT OID	specifies the attributes of the object type that will be used as a key to uniquely identify each row in the object view. In most cases these attributes correspond to the primary-key columns of the base table.
	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. A view's query can be any SELECT statement without the ORDER BY or FOR UPDATE clauses. Its select list can contain up to 1000 expressions. See "The View Query", "Join Views", and "Subqueries".
	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.
	Object types, REF object_type, LOBs, VARRAYs, and nested tables are valid column types.
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.

 

Using Views

A view is a logical table that allows you to access data from other tables and views. A view contains no data itself. The tables upon which a view is based are called base tables.

Views are used for the following purposes:

• To provide an additional level of table security, by restricting access to a predetermined set of rows and/or columns of a base table.
• To hide data complexity. For example, a view may be used to act as one table when actually several tables are used to construct the result.
• To present data from another perspective. For example, views provide a means of renaming columns without actually changing the base table's definition.
• To cause Oracle to perform some operations, such as joins, on the database containing the view, rather than another database referenced in the same SQL statement. (See also "Join Views".)

You can use a view anywhere you can use a table in these SQL statements:

• COMMENT
• DELETE
• INSERT
• LOCK TABLE
• UPDATE
• SELECT

The View Query

See "Subqueries" for the syntax of the view's query in the description of the subquery syntax. Note the following caveats:

• A view's query cannot select the CURRVAL or NEXTVAL pseudocolumns.
• If a view's query selects the ROWID, ROWNUM, or LEVEL pseudocolumns, they must have aliases in the view's query.
• You can define a view with a query that uses an asterisk (*) to select all the columns of a table:

       CREATE VIEW emp_vu
         AS SELECT * FROM emp; 

Oracle translates the asterisk into a list of all the columns in the table at the time the CREATE VIEW statement is issued. If you subsequently add new columns to the table, the view will not contain these columns unless you recreate the view by issuing another CREATE VIEW statement with the OR REPLACE option. Oracle recommends that you explicitly specify all columns in the select list of a view query, rather than use the asterisk.

• You can create views that refer to remote tables and views by using database links in the view query. You should qualify any remote table or view referenced in the view query with the name of the schema containing it. Further, you should define any database links used in the view query using the CONNECT TO clause of the CREATE DATABASE LINK command.

The above caveats also apply to the query for a snapshot.

A view is inherently updatable if it can be inserted, updated, or deleted without using INSTEAD OF triggers and if it conforms to the restrictions listed below. However, if the view query contains any of the following constructs, it is not inherently updatable, and therefore you cannot perform inserts, updates, or deletes on the view except through INSTEAD OF triggers:

• set operators
• group functions
• GROUP BY, CONNECT BY, or START WITH clauses
• the DISTINCT operator
• joins (a subset of join views are updatable)

Note that if a view contains pseudocolumns or expressions, you can update the view only with an UPDATE statement that does not refer to any of the pseudocolumns or expressions.

For more information about updating object views or relational views that support object types, see Oracle8 Application Developer's Guide.

Join Views

A join view is a view with a subquery containing a join. The restrictions discussed in "The View Query" also apply to join views.

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

In the above example, note the 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 Oracle8 Application Developer's Guide.

Note:   If there were NOT NULL columns in the base EMP table that were not specified in the view subquery, then you could not insert into the table using the view.

   

ROWID Selection

You can select a ROWID from a join view, provided that there is one and only one key-preserved table in the join. The ROWID of that table becomes the ROWID of the view.

Updatable Join Views

A join view is a view that contains a join. Join views are updatable under the conditions discussed in this section.

A key-preserved table is a table in a join view, all of whose key columns are present as keys in the join view. This means the keys must not only be in the join view, but must still be unique and not null in the join view. This implies that a key-preserved table generally cannot be an outer-joined table. A key-preserved table could be an outer-joined table only if the outer join did not in fact generate any nulls. This, however, is a function of the data and therefore inadmissible as a basis for operations.

Therefore, you can execute the DML statements INSERT, UPDATE, and DELETE on a join view only provided that all of the following are true:

• The DML statement affects only one of the tables underlying the join.
• If the statement is UPDATE, then all columns updated are extracted from a key-preserved table. In addition, if the view has the CHECK OPTION, join columns and columns taken from tables that are referenced more than once in the view are shielded from UPDATE.
• If the statement is DELETE, then there is one and only one key-preserved table in the join. This table may be present more than once in the join, unless the view has the CHECK OPTION.
• If the statement is INSERT, then all columns into which values are inserted come from a key-preserved table, and the view does not have the CHECK OPTION.

Partition Views

Partition views were introduced in Release 7.3 to provide partitioning capabilities for applications requiring them. Partition views are supported in Oracle8 so that you can upgrade applications from Release 7.3 without any modification. In most cases, subsequent to migration to Oracle8 you will want to migrate partition views into partitions (see Oracle8 Migration and Oracle8 Application Developer's Guide).

With Oracle8, you can use the CREATE TABLE command to create partitioned tables easily. Partitioned tables offer the same advantages as partition views, while also addressing their shortcomings. Oracle recommends that you use partitioned tables rather than partition views in most operational environments. For more information about partitioned tables, see CREATE TABLE.

Examples

Example I

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; 

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

Example II

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.

Example III

The following statement creates a read-only view named CLERKS of all clerks in the EMP table; only the employee's IDs, names, and department numbers 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 Views

An object view synthesizes objects based on queries of relational or object tables. The number of elements in the view subquery's select list must be the same as the number of top-level attributes of the object type. Each select element's datatype must be the same as (or convertible to) the corresponding top-level attribute.

The set of attributes in the WITH OBJECT OID clause must yield a unique key for the objects in the object 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.

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.

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

For more information about object views, refer to Oracle8 Concepts and the Oracle8 Application Developer's Guide.

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 OID (empno)
  AS SELECT empno, ename, job, mgr, hiredate, sal, comm 
     FROM emp;

Related Topics

CREATE TABLE

CREATE SYNONYM

CREATE TYPE

DROP VIEW

RENAME

SELECT

---

DEALLOCATE UNUSED clause

Purpose

To specify the amount of unused space to deallocate from extents. See also "Deallocating Unused Space".

Prerequisites

This clause can be used only in the following commands:

• ALTER CLUSTER
• ALTER TABLE
• ALTER INDEX

Syntax

 

Keywords and Parameters

KEEP	specifies the amount of unused space to keep.
	integer	the number of bytes to keep. You can use K or M to specify the size in kilobytes or megabytes.

 

Deallocating Unused Space

For more information on the administration of schema objects, see Oracle8 Administrator's Guide.

Use the DEALLOCATE clause to reclaim unused space in extents in a cluster, table or index for reuse by other objects in the tablespace. Oracle credits the amount of the released space to the user quota for the tablespace in which the deallocation occurs.

Unused space is deallocated from the end of the object toward the high-water mark at the beginning of the object. If an extent is completely contained in the deallocation, then the whole extent is freed for reuse. If an extent is partially contained in the deallocation, then the used part up to the high water mark becomes the extent, and the remaining unused space is freed for reuse.

The exact amount of space freed depends on the values of the INITIAL, MINEXTENTS, and NEXT parameters (are described in STORAGE clause).

• If you omit the KEEP option and the high-water mark is above the size of INITIAL and MINEXTENTS, then all unused space above the high-water mark is freed. When the high water mark is less than the size of INITIAL or MINEXTENTS, then all unused space above MINEXTENTS is freed.
• If you use the KEEP option, then the specified amount of space is kept and the remaining space is freed. When the remaining number of extents is less than MINEXTENTS, then MINEXTENTS is adjusted to the new number of extents. If the initial extent becomes smaller than INITIAL, then INITIAL is adjusted to the new size.
• In either case, NEXT is set to the size of the last extent that was deallocated.

Example

The following command frees all unused space for reuse in table EMP, where the high-water mark is above MINEXTENTS:

ALTER TABLE emp
    DEALLOCATE UNUSED

Related Topics

ALTER TABLE

Oracle8 Administrator's Guide

Oracle8 Concepts

---

DELETE

Purpose

To remove rows from a table, a partitioned table, a view's base table, or a view's partitioned base table. See also "Using DELETE".

Note:   Descriptions of commands and clauses preceded by  are available only if the Oracle objects option is installed on your database server.

   

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 any view's base table.

If the SQ92_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).

Syntax

 

subquery: See "Subqueries".
 

Keywords and Parameters

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 or view	is the name of a table from which the rows are to be deleted. If you specify view, Oracle deletes rows from the view's base table.
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.
PARTITION (partition_name)	specifies partition-level row deletes for table. The partition_name is the name of the partition within table targeted for deletes. See also "Deleting from a Single Partition".
THE	informs Oracle that the column value returned by the subquery is a nested table, not a scalar value. A subquery prefixed by THE is called a flattened subquery. "Using Flattened Subqueries".
	subquery	specifies which data is selected for deletion. Oracle executes the subquery and then uses the resulting rows as a table in the FROM clause. The subquery cannot query a table that appears in the same FROM clause as the subquery. See "Subqueries".
alias	is an alias assigned to the table, view or subquery. Aliases are generally used in DELETE statements with correlated queries.
WHERE condition	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.
returning_clause	retrieves the rows affected by the DELETE statement. You can retrieve only scalar, LOB, ROWID, and REF types. See also "The RETURNING Clause".
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.
You cannot use the RETURNING clause with parallel DML or with remote objects.

 

Using DELETE

You can use comments in a DELETE statement to pass instructions, or hints, to the Oracle optimizer. The optimizer uses hints to choose an execution plan for the statement. For more information on hints, see Oracle8 Tuning.

You can place a parallel hint immediately after the DELETE keyword to parallelize both the underlying scan and DELETE operations. For detailed information about parallel DML, see Oracle8 Tuning, Oracle8 Parallel Server Concepts & Administration, and Oracle8 Concepts.

All table and index space released by the deleted rows is retained by the table and index. You cannot delete from a view if the view's defining query contains one of the following constructs:

• set operator
• GROUP BY clause
• group function
• DISTINCT operator

Issuing a DELETE statement against a table fires any DELETE triggers defined on the table.

Example I

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

DELETE FROM temp_assign;

Example II

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;

Example III

The following statement has the same effect as Example II:

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

Example IV

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;

Example V

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;

Deleting from a Single Partition

You do not need to 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. To target a single partition of a partitioned table whose values you want to change, specify the PARTITION clause. This syntax is less cumbersome than using a WHERE clause in some cases.

Example

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

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

The RETURNING Clause

You can use a RETURNING clause to return values from deleted columns, and thereby eliminate the need to perform a SELECT 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 using a DELETE statement with the RETURNING clause to remove multiple rows, values from expressions, ROWID, and REFs involving the deleted rows are stored 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.

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;

Related Topics

UPDATE

---

DISABLE clause

Purpose

To disable an integrity constraint or all triggers associated with a table:

• If you disable an integrity constraint, Oracle does not enforce it. However, disabled integrity constraints appear in the data dictionary along with enabled integrity constraints.
• If you disable a trigger, Oracle does not fire it if its triggering condition is satisfied.

See also "Using the DISABLE Clause".

Prerequisites

A DISABLE clause that disables an integrity constraint can appear in either a CREATE TABLE or ALTER TABLE command. To disable an integrity constraint, you must have the privileges necessary to issue one of these commands. For information on these privileges, see CREATE TABLE and ALTER TABLE.

For an integrity constraint to appear in a DISABLE clause, either

• the integrity constraint must be defined in the containing statement, or
• the integrity constraint must already have been defined and enabled in previously issued statements.

A DISABLE clause that disables triggers can appear only in an ALTER TABLE statement. To disable triggers with a DISABLE clause, you must have the privileges necessary to issue the ALTER TABLE statement. For information on these privileges, see ALTER TABLE. Also, the triggers must be in your own schema or you must have ALTER ANY TRIGGER system privilege.

Syntax

 

Keywords and Parameters

UNIQUE	disables the UNIQUE constraint defined on the specified column or combination of columns.
PRIMARY KEY	disables the table's PRIMARY KEY constraint.
CONSTRAINT	disables the integrity constraint with the name constraint.
CASCADE	disables any integrity constraints that depend on the specified integrity constraint. To disable a primary or unique key that is part of a referential integrity constraint, you must specify this option.
ALL TRIGGERS	disables all triggers associated with the table. This option can appear only in a DISABLE clause in an ALTER TABLE statement, not in a CREATE TABLE statement.

 

Using the DISABLE Clause

Use the DISABLE clause to disable either:

• a single integrity constraint or
• all triggers associated with a table.

To disable a single trigger, use the DISABLE option of the ALTER TRIGGER command.

Disabling Integrity Constraints

You disable an integrity constraint by naming it in a DISABLE clause of either a CREATE TABLE or ALTER TABLE statement. You can define an integrity constraint with a CONSTRAINT clause and disable it with a DISABLE clause together in the same statement. You can also define an integrity constraint in one statement and subsequently disable it in another.

You can also disable an integrity constraint with the DISABLE keyword in the CONSTRAINT clause that defines the integrity constraint. For information on this keyword, see the CONSTRAINT clause.

How Oracle Disables Integrity Constraints

If you disable an integrity constraint, Oracle does not enforce it. If you define an integrity constraint and disable it, Oracle does not apply it to existing rows of the table, although Oracle does store it in the data dictionary along with enabled integrity constraints. Also, Oracle can execute data manipulation language (DML) statements that change table data and violate a disabled integrity constraint.

If you disable a UNIQUE or PRIMARY KEY constraint that was previously enabled, Oracle drops the index that enforces the constraint.

You can enable a disabled integrity constraint with the ENABLE clause.

Disabling Referenced Keys in Referential Integrity Constraints

To disable a UNIQUE or PRIMARY KEY constraint that identifies the referenced key of a referential integrity constraint (foreign key), you must also disable the foreign key. To do so, use the CASCADE option of the DISABLE clause.

You cannot enable a foreign key that references a unique or primary key that is disabled.

Example I

The following statement creates the DEPT table and defines a disabled PRIMARY KEY constraint:

CREATE TABLE dept 
    (deptno  NUMBER(2)  PRIMARY KEY, 
     dname    VARCHAR2(10), 
     loc      VARCHAR2(9) ) 
     DISABLE  PRIMARY KEY;

Since the primary key is disabled, you can add to the table rows that violate the primary key. For example, you can add departments with null department numbers or multiple departments with the same department number.

Example II

The following statement defines and disables a CHECK constraint on the EMP table:

ALTER TABLE emp 
    ADD (CONSTRAINT check_comp  CHECK (sal + comm <= 5000) )
    DISABLE CONSTRAINT check_comp;

The constraint CHECK_COMP ensures that no employee's total compensation exceeds $5000. The constraint is disabled, so you can increase an employee's compensation above this limit.

Example III

Consider a referential integrity constraint involving a foreign key on the combination of the AREACO and PHONENO columns of the PHONE_CALLS table. The foreign key references a unique key on the combination of the AREACO and PHONENO columns of the CUSTOMERS table. The following statement disables the unique key on the combination of the AREACO and PHONENO columns of the CUSTOMERS table:

ALTER TABLE customers 
    DISABLE UNIQUE (areaco, phoneno) CASCADE; 

The unique key in the CUSTOMERS table is referenced by the foreign key in the PHONE_CALLS table, so you must use the CASCADE option to disable the unique key. This option disables the foreign key as well.

How to Disable Triggers

You can disable all triggers associated with a table by using the ALL TRIGGERS option in a DISABLE clause of an ALTER TABLE statement. After you disable a trigger, Oracle does not fire the trigger when a triggering statement meets the condition of the trigger restriction.

Example

The following statement disables all triggers associated with the EMP table:

ALTER TABLE emp 
    DISABLE ALL TRIGGERS;

Related Topics

ALTER TABLE

ALTER TRIGGER

CONSTRAINT clause

CREATE TABLE

CREATE TRIGGER

DISABLE clause

---

DROP clause

Purpose

To remove an integrity constraint from the database. See also "Removing Integrity Constraints".

Prerequisites

The DROP clause can appear only in an ALTER TABLE statement. To drop an integrity constraint, you must have the privileges necessary to issue an ALTER TABLE statement. For information on these privileges, see ALTER TABLE.

Syntax

 

Keywords and Parameters

PRIMARY KEY	drops the table's PRIMARY KEY constraint.
UNIQUE	drops the UNIQUE constraint on the specified columns.
CONSTRAINT	drops the integrity constraint named constraint.
CASCADE	drops all other integrity constraints that depend on the dropped integrity constraint.

 

Removing Integrity Constraints

You can drop an integrity constraint by naming it in a DROP clause of an ALTER TABLE statement. When you drop an integrity constraint, Oracle stops enforcing the integrity constraint and removes it from the data dictionary.

You cannot drop a unique or primary key that is part of a referential integrity constraint without also dropping the foreign key. You can drop the referenced key and the foreign key together by specifying the referenced key with the CASCADE option in the DROP clause. If you omit CASCADE, Oracle does not drop the unique or primary key constraint if any foreign key references it.

Example I

The following statement drops the primary key of the DEPT table:

ALTER TABLE dept 
    DROP PRIMARY KEY CASCADE; 

If you know that the name of the PRIMARY KEY constraint is PK_DEPT, you could also drop it with the following statement:

ALTER TABLE dept
    DROP CONSTRAINT pk_dept CASCADE; 

The CASCADE option drops any foreign keys that reference the primary key.

Example II

The following statement drops the unique key on the DNAME column of the DEPT table:

ALTER TABLE dept 
    DROP UNIQUE (dname); 

Note that the DROP clause in this example omits the CASCADE option. Because of this omission, Oracle does not drop the unique key if any foreign key references it.

Related Topics

ALTER TABLE

CONSTRAINT clause

---

DROP CLUSTER

Purpose

To remove a cluster from the database. See "Restrictions".

Prerequisites

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

Syntax

 

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.
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 option and such referential integrity constraints exist, Oracle returns an error message and does not drop the cluster.

 

Restrictions

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

You cannot uncluster an individual table. To create an unclustered table identical to an existing clustered table, follow the following steps:

1. Create a new table with the same structure and contents as the old one but with no CLUSTER option.
2. Drop the old table.
3. Use the RENAME command to give the new table the name of the old one.

Grants on the old clustered table do not apply to the new unclustered table and must be regranted.

Example

This command 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;

Related Topic

DROP TABLE

---