Oracle8i Application Developer's Guide - Object-Relational Features
Release 2 (8.1.6)

Part Number A76976-01





Go to previous page Go to next page

Managing Oracle Objects

This chapter explains how Oracle objects work in combination with the rest of the database, and how to perform DML and DDL operations on them. It contains the following major sections:

Using Object Types and References

This section describes object types and references, including:

Null Objects and Attributes

Many things associated with objects can be null: a table column, object, object attribute, collection, or collection element. This means that the item is initialized to NULL or is not initialized. Usually, the value of the item is not yet known but might become available later.

An object whose value is NULL is called atomically null. In addition, attributes of an object can be null. These two uses of nulls are different. When all the attributes of an object are null, you can still change the attributes and call methods. When an object is atomically null, you cannot do very much with it at all.

For example, consider the CONTACTS table defined as follows:

  name        VARCHAR2(30),
  phone       VARCHAR2(20) );

CREATE TABLE contacts (
  contact     person
  date        DATE );

The statement

  person (NULL, NULL),
 '24 Jun 1997' );

gives a different result from

 '24 Jun 1997' ); 

In both cases, Oracle allocates space in CONTACTS for a new row and sets its DATE column to the value given. In the first case, Oracle allocates space for an object in the PERSON column and sets each of its attributes to NULL. In the second case, it sets the PERSON column to NULL and does not allocate space for an object.

In some cases, you can omit checks for null values. A table row or row object cannot be null. A nested table of objects cannot contain an element whose value is NULL.

A nested table or array can be null, so you do need to handle that condition. A null collection is different from an empty one, that is, a collection containing no elements.

Default Values for Objects and Collections

When you declare a table column to be of an object type or collection type, you can include a DEFAULT clause. This provides a value to use in cases where you do not explicitly specify a value for the column. The default clause must contain a literal invocation of the constructor method for that object or collection.

A literal invocation of a constructor method is a call to the constructor method in which any arguments are either literals, or further literal invocations of constructor methods. No variables or functions are allowed.

For example, consider the following statements:

  id        NUMBER
  name      VARCHAR2(30),
  address   VARCHAR2(30) );

CREATE TYPE people AS TABLE OF person;

The following is a literal invocation of the constructor method for the nested table type PEOPLE:

people ( person(1, 'John Smith', '5 Cherry Lane'), 
         person(2, 'Diane Smith', NULL) )

The following example shows how to use literal invocations of constructor methods to specify defaults:

CREATE TABLE department (
  d_no    CHAR(5) PRIMARY KEY,
  d_name  CHAR(20),
  d_mgr   person DEFAULT person(1,'John Doe',NULL),
  d_emps  people DEFAULT people() )
  NESTED TABLE d_emps STORE AS d_emps_tab;

Note that the term PEOPLE( ) is a literal invocation of the constructor method for an empty PEOPLE table.

Constraints for Object Tables

You can define constraints on an object table just as you can on other tables.

You can define constraints on the leaf-level scalar attributes of a column object, with the exception of REFs that are not scoped.

The following examples illustrate the possibilities.

The first example places a primary key constraint on the SSNO column of the object table PERSON_EXTENT:

CREATE TYPE location (
  building_no NUMBER,
  city        VARCHAR2(40) );

CREATE TYPE person (
  ssno        NUMBER,
  name        VARCHAR2(100),
  address     VARCHAR2(100),
  office      location );

CREATE TABLE person_extent OF person (
  ssno        PRIMARY KEY );

The DEPARTMENT table in the next example has a column whose type is the object type LOCATION defined in the previous example. The example defines constraints on scalar attributes of the LOCATION objects that appear in the DEPT_LOC column of the table.

CREATE TABLE department (
  deptno      CHAR(5) PRIMARY KEY, 
  dept_name   CHAR(20),
  dept_mgr    person,
  dept_loc    location,
  CONSTRAINT  dept_loc_cons1
      UNIQUE (dept_loc.building_no,,
  CONSTRAINT  dept_loc_cons2
       CHECK ( IS NOT NULL) );

Indexes for Object Tables and Nested Tables

You can define indexes on an object table or on the storage table for a nested table column or attribute, just as you can on other tables.

You can define indexes on leaf-level scalar attributes of column objects, as shown in the following example. You can only define indexes on REF attributes or columns if the REF is scoped.

Here, DEPT_ADDR is a column object, and CITY is a leaf-level scalar attribute of DEPT_ADDR that we want to index:

CREATE TABLE department (
  deptno      CHAR(5) PRIMARY KEY, 
  dept_name   CHAR(20),
  dept_addr   address );

CREATE INDEX  i_dept_addr1
          ON  department (;

Wherever Oracle expects a column name in an index definition, you can also specify a scalar attribute of an object column.

Triggers for Object Tables

You can define triggers on an object table just as you can on other tables. You cannot define a trigger on the storage table for a nested table column or attribute.

You cannot modify LOB values in a trigger body. Otherwise, there are no special restrictions on using object types with triggers.

The following example defines a trigger on the PERSON_EXTENT table defined in an earlier section:

CREATE TABLE movement (
     ssno        NUMBER,
     old_office  location,
     new_office  location );

             OF  office
             ON  person_extent
           WHEN = 'REDWOOD SHORES'
     IF = 600 THEN
      INSERT INTO movement (ssno, old_office, new_office)
       VALUES (:old.ssno,,;
     END IF;

Rules for REF Columns and Attributes

In Oracle, a REF column or attribute can be unconstrained or constrained using a SCOPE clause or a referential constraint clause. When a REF column is unconstrained, it may store object references to row objects contained in any object table of the corresponding object type.

Oracle does not ensure that the object references stored in such columns point to valid and existing row objects. Therefore, REF columns may contain object references that do not point to any existing row object. Such REF values are referred to as dangling references. Currently, Oracle does not permit storing object references that contain a primary-key based object identifier in unconstrained REF columns.

A REF column may be constrained to be scoped to a specific object table. All the REF values stored in a column with a SCOPE constraint point at row objects of the table specified in the SCOPE clause. The REF values may, however, be dangling.

A REF column may be constrained with a REFERENTIAL constraint similar to the specification for foreign keys. The rules for referential constraints apply to such columns. That is, the object reference stored in these columns must point to a valid and existing row object in the specified object table.

UNIQUE or PRIMARY KEY constraints cannot be specified for REF columns. However, you can specify NOT NULL constraints for such columns.

Name Resolution

Oracle SQL lets you omit table names in some relational operations. For example, if ASSIGNMENT is a column in PROJECTS and TASK is a column in DEPTS, you can write:

FROM projects 
  (SELECT * FROM  depts
            WHERE assignment = task); 

Oracle determines which table each column belongs to.

You can qualify the column names with table names or table aliases to make things more maintainable:

  (SELECT * FROM  depts WHERE projects.assignment = depts.task);

  (SELECT * FROM  depts dp WHERE pj.assignment = dp.task);

In some cases, object-relational features require you to specify the table aliases.

When Table Aliases are Required

Using unqualified names can lead to problems. If you add an ASSIGNMENT column to the second table (DEPTS) and forget to change the query, Oracle automatically recompiles the query and the new version uses the ASSIGNMENT column from the DEPTS table. This situation is called inner capture.

To avoid inner capture and similar misinterpretations of the intended meanings of SQL statements, Oracle requires you to use table aliases to qualify references to methods or attributes of objects.

The same requirement applies to attribute references via REFs. This requirement is called the capture avoidance rule.

For example, consider the following statements:

CREATE TABLE ptab1 OF person;
CREATE TABLE ptab2 (c1 person);

These define an object type PERSON and two tables. The first is an object table for objects of type PERSON. The second has a single column of type PERSON.

Now consider the following queries:

SELECT      ssno FROM ptab1   ;  --Correct
SELECT   c1.ssno FROM ptab2   ;  --Wrong
SELECT p.c1.ssno FROM ptab2 p ;  --Correct

You must qualify references to object attributes with table aliases rather than table names, even if the table names are further qualified by schema names.

For example, the following expression tries to refer to the SCOTT schema, PROJECTS table, ASSIGNMENT column, and DUEDATE attribute of that column. But it is not allowed because PROJECTS is a table name, not an alias.


Table aliases should be unique throughout a query and should not be the same as schema names that could legally appear in the query.


We recommend that you define table aliases in all UPDATE, DELETE, and SELECT statements and subqueries and use them to qualify column references, whether or not the columns contain object types. 

Method Calls without Arguments

Methods are functions or subroutines. The proper syntax for invoking them uses parentheses following the method name to enclose any calling arguments. In order to avoid ambiguities, Oracle requires empty parentheses for method calls that do not have arguments.

For example, if TB is a table with column C of object type T, and T has a method m that does not take arguments, the following query illustrates the correct syntax:

SELECT p.c.m() FROM tb p;

This differs from the rules for PL/SQL functions and procedures, where the parentheses are optional for calls that have no arguments.

Using Collections

This section describes the use of collections, including:

Querying Collections

In Oracle8i, a collection column may be queried using the TABLE expression. For example, a nested table column (PROJECTS) of the table (EMPLOYEES) can be queried as follows:

SELECT * FROM TABLE(SELECT t.projects FROM employees t WHERE t.eno = 1000);

SELECT t.eno, CURSOR(SELECT * FROM TABLE(t.projects)) FROM employees t;

The TABLE expression can be used to query any collection value expression, including transient values such as variables and parameters.


The TABLE expression takes the place of THE subquery introduced in a previous release. The THE subquery expression will eventually be deprecated. 

Collection Unnesting

Many tools and applications are not equipped to deal with collection types, and require a flattened view of the data. In order to use these tools to view Oracle collection data, you must has to be unnest or flatten the collection attribute of a row into one or more relational rows. You do this by joining the rows of the nested table with the row that contains the nested table.

Consider the following object-relational schema, where we define a type that contains a nested table, and specify the name by which we will access the nested table:

CREATE TYPE emp_set_t IS NESTED TABLE of emp_t;
CREATE TYPE dept_t(deptno NUMBER, emps emp_set_t);
CREATE TABLE depts OF dept_t NESTED TABLE emps STORE AS depts_emps;

The following query unnests the data in the EMPS column with respect to the DEPT table by augmenting every row of EMPS with its parent DEPTS row:

SELECT d.deptno, e.* FROM depts d, TABLE(d.emps) e;

Oracle8i also supports the following syntax to produce outer-join results:

SELECT d.*, e.* FROM depts d, TABLE(d.emps)(+) e;

The (+) indicates that the dependent join between DEPTS and D.EMPS should be NULL-augmented. That is, there will be rows of DEPTS in the output for which D.EMPS is NULL or empty, with NULL values for columns corresponding to D.EMPS.

DML on Collections

Oracle supports the following DML operations on nested table columns:

Oracle does not support piecewise updates on VARRAY columns. However, VARRAY columns can be inserted into or updated as an atomic unit.

For piecewise updates of nested table columns, the DML statement identifies the nested table value to be operated on by using the TABLE expression. Note that DML operations on a nested table value are serialized. That is, when a nested table value is operated on by a DML statement in a transaction, modifications to the same nested table value from other transactions are blocked until after the transaction terminates.

The following DML statements demonstrate piecewise operations on nested table columns.

                  FROM       employees e
                  WHERE      e.eno = 100)
   VALUES (1, `Project Neptune');

                 FROM        employees e
                 WHERE       e.eno = 100) p
   SET VALUE(p) = project_t(1, `Project Pluto')
   WHERE p.pno = 1;

                  FROM        employee e
                  WHERE       e.eno = 100) p
   WHERE p.pno = 1;

Privileges on Object Types and Their Methods

Privileges for object types exist at the system level and schema object level.

System Privileges

Oracle defines the following system privileges for object types:

The CONNECT and RESOURCE roles include the CREATE TYPE system privilege. The DBA role includes all of the above privileges.

Schema Object Privileges

The only schema object privilege that applies to object types is EXECUTE.

EXECUTE on a object type allows you to use the type to:

EXECUTE lets you invoke the type's methods, including the constructor.

Method execution and the associated permissions are the same as for stored PL/SQL procedures.

Using Types in New Types or Tables

In addition to the permissions detailed in the previous sections, you need specific privileges to:

You must have the EXECUTE ANY TYPE system privilege, or you must have the EXECUTE object privilege for any type you use in defining a new type or table. You must have received these privileges explicitly, not through roles.

If you intend to grant access to your new type or table to other users, you must have either the required EXECUTE object privileges with the GRANT option or the EXECUTE ANY TYPE system privilege with the option WITH ADMIN OPTION. You must have received these privileges explicitly, not through roles.


Assume that three users exist with the CONNECT and RESOURCE roles: USER1, USER2, and USER3.

USER1 performs the following DDL in the USER1 schema:

GRANT EXECUTE ON type1 TO user2;

USER2 performs the following DDL in the USER2 schema:

CREATE TABLE tab1 OF user1.type1;
CREATE TYPE type3 AS OBJECT ( attr3 user1.type2 );
CREATE TABLE tab2 (col1 user1.type2 );

The following statements succeed, because USER2 has EXECUTE on USER1's TYPE2 with the GRANT option:

GRANT EXECUTE ON type3 TO user3;
GRANT SELECT on tab2 TO user3;

However, the following grant fails, because USER2 does not have EXECUTE on USER1.TYPE1 with the GRANT option:

GRANT SELECT ON tab1 TO user3;

USER3 can successfully perform the following actions:

CREATE TYPE type4 AS OBJECT (attr4 user2.type3);
CREATE TABLE tab3 OF type4;

Privileges on Type Access and Object Access

While object types only make use of EXECUTE privilege, object tables use all the same privileges as relational tables:

Similar table and column privileges regulate the use of table columns of object types.

Selecting columns of an object table does not require privileges on the type of the object table. Selecting the entire row object, however, does.

Consider the following schema:

CREATE TYPE emp_type as object (
  eno    NUMBER,
  ename  CHAR(31),
  eaddr  addr_t );

CREATE TABLE emp OF emp_type;

and the following two queries:

SELECT eno, ename FROM emp;

For either query, Oracle checks the user's SELECT privilege for the EMP table. For the first query, the user needs to obtain the EMP_TYPE type information to interpret the data. When the query accesses the EMP_TYPE type, Oracle checks the user's EXECUTE privilege.

Execution of the second query, however, does not involve named types, so Oracle does not check type privileges.

Additionally, using the schema from the previous section, USER3 can perform the following queries:

SELECT tab1.col1.attr2 from user2.tab1 tab1;
SELECT t.attr4.attr3.attr2 FROM tab3 t;

Note that in both selects by USER3, USER3 does not have explicit privileges on the underlying types, but the statement succeeds because the type and table owners have the necessary privileges with the GRANT option.

Oracle checks privileges on the following requests, and returns an error if the requestor does not have the privilege for the action:

Oracle does not provide column level privileges for object tables.

Dependencies and Incomplete Types

Types can depend upon each other for their definitions. For example, you might want to define object types EMPLOYEE and DEPARTMENT in such a way that one attribute of EMPLOYEE is the department the employee belongs to and one attribute of DEPARTMENT is the employee who manages the department.

Types that depend on each other in this way, either directly or via intermediate types, are called mutually dependent. A diagram of mutually dependent types, with arrows representing the dependencies, always reveals a path of arrows starting and ending at one of the types.

To define such a cyclic dependency, you must use REFs for at least one branch of the cycle.

For example, you can define the following types:

CREATE TYPE department;

  name    VARCHAR2(30),
  dept    REF department,
  supv    REF employee );

CREATE TYPE emp_list AS TABLE OF employee;

  name    VARCHAR2(30),
  mgr     REF employee,
  staff   emp_list );

This is a legal set of mutually dependent types and a legal sequence of SQL DDL statements. Oracle compiles it without errors. The first statement:

CREATE TYPE department;

is optional. It makes the compilation proceed without errors. It establishes DEPARTMENT as an incomplete object type. A REF to an incomplete object type compiles without error, so the compilation of EMPLOYEE proceeds.

When Oracle reaches the last statement, which completes the definition of DEPARTMENT, all of the components of DEPARTMENT have compiled successfully, so the compilation finishes without errors.

Without the optional declaration of DEPARTMENT as an incomplete type, EMPLOYEE compiles with errors. Oracle then automatically adds EMPLOYEE to its library of schema objects as an incomplete object type. This makes the declarations of EMP_LIST and DEPARTMENT compile without errors. When EMPLOYEE is recompiled after EMP_LIST and DEPARTMENT are complete, EMPLOYEE compiles without errors and becomes a complete object type.

Completing Incomplete Types

Once you have declared an incomplete object type, you must complete it as an object type. You cannot, for example, declare it to be a table type or an array type. The only alternative is to drop the type.

This is also true if Oracle has made the type an incomplete object type for you--as it did when EMPLOYEE failed to compile in the previous section.

Type Dependencies of Tables

The SQL commands REVOKE and DROP TYPE return an error and abort if the type referred to in the command has tables or other types that depend on it.

The FORCE option with either of these commands overrides that behavior. The command succeeds and the affected tables or types become invalid.

If a table contains data that relies on a type definition for access, any change to the type causes the table's data to become inaccessible. This happens if privileges required by the type are revoked or if the type or a type it depends on is dropped. The table then becomes invalid and cannot be accessed.

A table that is invalid because of missing privileges automatically becomes valid and accessible if the required privileges are re-granted.

A table that is invalid because a type it depends on has been dropped can never be accessed again. The only permissible action is to drop the table.

Import/Export/Load of Object Types

The Export and Import utilities move data into and out of Oracle databases. They also back up or archive data and aid migration to different releases of the Oracle RDBMS.

Export and Import support object types. Export writes object type definitions and all of the associated data to the dump file. Import then re-creates these items from the dump file.

The SQL*Loader supports loading row objects, column objects and objects with collections and references. In Oracle8i, only conventional path loading is supported for objects.

An alternative to conventional path loading is to first load the data into relational tables using direct path loading, and then create the object tables and tables with column objects using CREATE TABLE...AS SELECT commands. However, with this approach you need enough space to hold as much as twice the actual data.

See Also:

Oracle8i Utilities for information about exporting, importing, and loading Oracle objects. 

Go to previous page Go to next page
Copyright © 1996-2000, Oracle Corporation.

All Rights Reserved.