7 Advanced Topics for Oracle Objects

The other chapters in this book discuss the topics that you need to get started with Oracle objects. The topics in this chapter are of interest once you start applying object-relational techniques to large-scale applications or complex schemas.

The chapter contains these topics:

Storage of Objects

Oracle automatically maps the complex structure of object types into the simple rectangular structure of tables.

Leaf-Level Attributes

An object type is like a tree structure, where the branches represent the attributes. Attributes that are objects sprout subbranches for their own attributes.

Ultimately, each branch ends at an attribute that is a built-in type; such as NUMBER, VARCHAR2, or REF; or a collection type, such as VARRAY or nested table. Each of these leaf-level attributes of the original object type is stored in a table column.

The leaf-level attributes that are not collection types are called the leaf-level scalar attributes of the object type.

How Row Objects Are Split Across Columns

In an object table, Oracle stores the data for every leaf-level scalar or REF attribute in a separate column. Each VARRAY is also stored in a column, unless it is too large. Oracle stores leaf-level attributes of nested table types in separate tables associated with the object table. You must declare these tables as part of the object table declaration. See "Internal Layout of VARRAYs" and "Internal Layout of Nested Tables".

When you retrieve or change attributes of objects in an object table, Oracle performs the corresponding operations on the columns of the table. Accessing the value of the object itself produces a copy of the object, by invoking the default constructor for the type, using the columns of the object table as arguments.

Oracle stores the system-generated object identifier in a hidden column. Oracle uses the object identifier to construct REFs to the object.

Hidden Columns for Tables with Column Objects

When a table is defined with a column of an object type, Oracle adds hidden columns to the table for the object type's leaf-level attributes. Each object-type column also has a corresponding hidden column to store the NULL information for the column objects (that is, the atomic nulls of the top-level and the nested objects).

Hidden Columns for Substitutable Columns and Tables

A substitutable column or object table has a hidden column not only for each attribute of the column's object type but also for each attribute added in any subtype of the object type. These columns store the values of those attributes for any subtype instances inserted in the substitutable column.

For example, a substitutable column of person_typ will have associated with it a hidden column for each of the attributes of person_typ: idno, name, and phone. It will also have hidden columns for attributes of the subtypes of person_typ. For example, the attributes dept_id and major (for student_typ) and number_hours (for part_time_student_typ).

When a subtype is created, hidden columns for attributes added in the subtype are automatically added to tables containing a substitutable column of any of the new subtype's ancestor types. These retrofit the tables to store data of the new type. If, for some reason, the columns cannot be added, creation of the subtype is rolled back.

When a subtype is dropped with the VALIDATE option to DROP TYPE, all such hidden columns for attributes unique to the subtype are automatically dropped as well if they do not contain data.

A substitutable column also has associated with it a hidden type discriminant column. This column contains an identifier, called a typeid, that identifies the most specific type of each object in the substitutable column. Typically, a typeid (RAW) is one byte, though it can be as big as four bytes for a large hierarchy.

You can find the typeid of a specified object instance using the function SYS_TYPEID. For example, suppose that the substitutable object table person_obj_table contains three rows, as shown in Example 2-17.

The query in Example 7-1 retrieves typeids of object instances stored in the table:

Example 7-1 Retrieving Typeids in a Table

SELECT name, SYS_TYPEID(VALUE(p)) typeid 
  FROM person_obj_table p;

------------------------------ ---------------------------
Bob Jones 01
Joe Lane 02
Kim Patel 03

The catalog views USER_TYPES, DBA_TYPES and ALL_TYPES contain a TYPEID column (not hidden) that gives the typeid value for each type. You can join on this column to get the type names corresponding to the typeids in a type discriminant column. See "SYS_TYPEID" for more information about SYS_TYPEID and typeids.


When Oracle constructs a REF to a row object, the constructed REF is made up of the object identifier (OID), some metadata of the object table, and, optionally, the ROWID.

The size of a REF in a column of REF type depends on the storage properties associated with the column. For example, if the column is declared as a REF WITH ROWID, Oracle stores the ROWID in the REF column. The ROWID hint is ignored for object references in constrained REF columns.

If column is declared as a REF with a SCOPE clause, the column is made smaller by omitting the object table metadata and the ROWID. A scoped REF is 16 bytes long.

If the object identifier is primary-key based, Oracle may create one or more internal columns to store the values of the primary key depending on how many columns comprise the primary key.


When a REF column references row objects whose object identifiers are derived from primary keys, we refer to it as a primary-key-based REF or pkREF. Columns containing pkREFs must be scoped or have a referential constraint.

Internal Layout of Nested Tables

The rows of a nested table are stored in a separate storage table. Each nested table column has a single associated storage table, not one for each row. The storage table holds all the elements for all of the nested tables in that column. The storage table has a hidden NESTED_TABLE_ID column with a system-generated value that lets Oracle map the nested table elements back to the appropriate row.

You can speed up queries that retrieve entire collections by making the storage table index-organized. Include the ORGANIZATION INDEX clause inside the STORE AS clause.

A nested table type can contain objects or scalars:

  • If the elements are objects, the storage table is like an object table: the top-level attributes of the object type become the columns of the storage table. But because a nested table row has no object identifier column, you cannot construct REFs to objects in a nested table.

  • If the elements are scalars, the storage table contains a single column called COLUMN_VALUE that contains the scalar values.

See "Nested Table Storage".

Internal Layout of VARRAYs

All the elements of a VARRAY are stored in a single column. Depending upon the size of the array, it may be stored inline or in a BLOB. See Storage Considerations for Varrays for details.

Creating Indexes on Typeids or Attributes

This section discusses the use of indexes on typeids and attributes.

Indexing a Type Discriminant Column

Using the SYS_TYPEID function, you can build an index on the hidden type discriminant column that every substitutable column has. The type discriminant column contains typeids that identify the most specific type of every object instance stored in the substitutable column. This information is used by the system to evaluate queries that use the IS OF predicate to filter by type, but you can access the typeids for your own purposes using the SYS_TYPEID function.


Generally, a type discriminant column contains only a small number of distinct typeids: at most, there can be only as many as there are types in the related type hierarchy. The low cardinality of this column makes it a good candidate for a bitmap index.

For example, the following statement creates a bitmap index on the type discriminant column underlying the substitutable contact column of table contacts. Function SYS_TYPEID is used to reference the type discriminant column:

CREATE BITMAP INDEX typeid_idx ON contacts (SYS_TYPEID(contact));

Indexing Subtype Attributes of a Substitutable Column

You can build an index on attributes of any of the types that can be stored in a substitutable column. Attributes of subtypes can be referenced in the CREATE INDEX statement by using the TREAT function to filter out types other than the desired subtype (and its subtypes); you then use the dot notation to specify the desired attribute.

For example, the following statement creates an index on the major attribute of all students in the contacts table. The declared type of the contact column is person_typ, of which student_typ is a subtype, so the column may contain instances of person_typ, student_typ, and subtypes of either one:

CREATE INDEX major1_idx ON contacts 
  (TREAT(contact AS student_typ).major);

student_typ is the type that first defined the major attribute: the person_typ supertype does not have it. Consequently, all the values in the hidden column for the major attribute are values for student_typ or parttimestudent_typ authors (a student_typ subtype). This means that the hidden column's values are identical to the values returned by the TREAT expression, which returns major values for all students, including student subtypes: both the hidden column and the TREAT expression list majors for students and nulls for authors of other types. The system exploits this fact and creates index major1_idx as an ordinary B-tree index on the hidden column.

Values in a hidden column are identical to the values returned by a TREAT expression like the preceding one only if the type named as the target of the TREAT function (student_typ) is the type that first defined the attribute. If the target of the TREAT function is a subtype that merely inherited the attribute, as in the following example, the TREAT expression will return non-null major values for the subtype (part-time students) but not for its supertype (other students).

CREATE INDEX major2_idx ON contacts 
  (TREAT(contact AS part_time_student_typ).major);

Here the values stored in the hidden column for major may be different from the results of the TREAT expression. Consequently, an ordinary B-tree index cannot be created on the underlying column. In a case like this, Oracle treats the TREAT expression like any other function-based expression and tries to create the index as a function-based index on the result.

The following example, like the previous one, creates a function-based index on the major attribute of part-time students, but in this case the hidden column for major is associated with a substitutable object table person_obj_table:

CREATE INDEX major3_idx ON person_obj_table p 
  (TREAT(VALUE(p) AS part_time_student_typ).major);

Type Evolution

Changing a object type is called type evolution. You can make the following changes to an object type:

  • Add and drop attributes

  • Add and drop methods

  • Modify a numeric attribute to increase its length, precision, or scale

  • Modify a varying length character attribute to increase its length

  • Change a type's FINAL and INSTANTIABLE properties

  • Modify limit and size of VARRAYs

  • Modify length, precision, and scale of collection elements

Changes to a type affect things that reference the type. For example, if you add a new attribute to a type, data in a column of that type must be presented so as to include the new attribute.

Schema objects that directly or indirectly reference a type and are affected by a change to it are called dependents of the type. A type can have these kinds of dependents:

  • Table

  • Type or subtype

  • Program unit (PL/SQL block): procedure, function, package, trigger

  • Indextype

  • View (including object view)

  • Function-based index

  • Operator

How a dependent schema object is affected by a change to a type depends on the dependent object and on the nature of the change to the type.

All dependent program units, views, operators and indextypes are marked invalid when a type is modified. The next time one of these invalid schema objects is referenced, it is revalidated using the new type definition. If the object recompiles successfully, it becomes valid and can be used again. Depending on the change to the type, function-based indexes may be dropped or disabled and need to be rebuilt.

If a type has dependent tables, then, for each attribute added to a type, one or more internal columns are added to the table depending on the new attribute's type. New attributes are added with NULL values. For each dropped attribute, the columns associated with that attribute are dropped. For each modified attribute, the length, precision, or scale of its associated column is changed accordingly.

These changes mainly involve updating the tables' metadata (information about a table's structure, describing its columns and their types) and can be done quickly. However, the data in those tables must be updated to the format of the new type version as well. Updating this data can be time-consuming if there is a lot of it, so the ALTER TYPE command has options to let you choose whether to convert all dependent table data immediately or to leave it in the old format to be converted piecemeal as it is updated in the course of business.

The CASCADE option for ALTER TYPE propagates a type change to dependent types and tables. See "ALTER TYPE Statement for Type Evolution". CASCADE itself has options that let you choose whether to convert table data to the new type format as part of the propagation: the option INCLUDING TABLE DATA converts the data; the option NOT INCLUDING TABLE DATA does not convert it. By default, the CASCADE option converts the data. In any case, table data is always returned in the format of the latest type version. If the table data is stored in the format of an earlier type version, Oracle converts the data to the format of the latest version before returning it, even though the format in which the data is actually stored is not changed until the data is rewritten.

You can retrieve the definition of the latest type from the system view USER_SOURCE. You can view definitions of all versions of a type in the USER_TYPE_VERSIONS view.

Changes Involved When a Type Is Altered

Only structural changes to a type affect dependent data and require the data to be converted. Changes that are confined to a type's method definitions or behavior (in the type body, where the type's methods are implemented) do not.

These possible changes to a type are structural:

  • Adding an attribute

  • Dropping an attribute

  • Modifying the length, precision, or scale of an attribute

  • Changing the finality of a type (which determines whether subtypes can be derived from it) from FINAL to NOT FINAL or from NOT FINAL to FINAL.

These changes result in new versions of the altered type and all its dependent types and require the system to add, drop, or modify internal columns of dependent tables as part of the process of converting to the new version.

When you make any of these kinds of changes to a type that has dependent types or tables, the effects of propagating the change are not confined only to metadata but affect data storage arrangements and require the data to be converted.

Besides converting data, you may also need to make other changes. For example, if a new attribute is added to a type, and the type body invokes the type's constructor, then each constructor in the type body must be modified to specify a value for the new attribute. Similarly, if a new method is added, then the type body must be replaced to add the implementation of the new method. The type body can be modified by using the CREATE OR REPLACE TYPE BODY statement.

Example 7-2 illustrates how to make a simple change to person_typ by adding one attribute and dropping another. The CASCADE keyword propagates the type change to dependent types and tables, but the phrase NOT INCLUDING TABLE DATA prevents conversion of the related data.

Example 7-2 Altering an Object Type by Adding and Dropping an Attribute

CREATE TABLE person_obj_table OF person_typ;

INSERT INTO person_obj_table 
  VALUES (person_typ(12, 'Bob Jones', '111-555-1212'));

SELECT value(p) FROM person_obj_table p;

PERSON_TYP(12, 'Bob Jones', '111-555-1212')
-- add the email attribute and drop the phone attribute
ALTER TYPE person_typ
  ADD ATTRIBUTE (email VARCHAR2(80)), 
-- Disconnect and reconnect to accommodate the type change
-- The data of table person_obj_table has not been converted yet, but
-- when the data is retrieved, Oracle returns the data based on
-- the latest type version. The new attribute is initialized to NULL.
SELECT value(p) FROM person_obj_table p;

PERSON_TYP(12, 'Bob Jones', NULL)

During SELECT statements, even though column data may be converted to the latest type version, the converted data is not written back to the column. If a certain user-defined type column in a table is retrieved often, you should consider converting that data to the latest type version to eliminate redundant data conversions. Converting is especially beneficial if the column contains a VARRAY attribute because a VARRAY typically takes more time to convert than an object or nested table column.

You can convert a column of data by issuing an UPDATE statement to set the column to itself. For example:

UPDATE dept_tab SET emp_array_col = emp_array_col;

You can convert all columns in a table by using ALTER TABLE with the UPGRADE INCLUDING DATA. For example:


Altering a Type by Adding a Nested Table Attribute

This section describes the steps required to make a complex change to a type. This change involves the addition of a nested table attribute to an object type that is already included in a nested table. When upgrading the affected nested table, the name of the new storage table is specified.

Assume we have the following schema based on the person_typ object type:

  1. Issue an ALTER TYPE statement to alter the type.

    The default behavior of an ALTER TYPE statement without any option specified is to check if there is any object dependent on the target type. The statement aborts if any dependent object exists. Optional keywords allow cascading the type change to dependent types and tables.

    With the ALTER TYPE statement in Example 7-3, all type and table checks are bypassed to save time and dependent objects are invalidated. Table data cannot be accessed until is validated.

    Example 7-3 Altering an Object Type by Adding a Nested Table Attribute

    -- Create and add a new nested table attribute to person_typ
    CREATE TYPE tasks_typ AS OBJECT (
      priority       VARCHAR2(2),
      description    VARCHAR2(30));
    CREATE TYPE tasks_nttab AS TABLE OF tasks_typ;
    ALTER TYPE person_typ ADD ATTRIBUTE tasks tasks_nttab
    -- Propagate the change to employee_store_nt
    -- Specify a storage name for the new nested table
    ALTER TABLE employee_store_nt
      UPGRADE NESTED TABLE tasks STORE AS tasks_nt;
  2. Use CREATE OR REPLACE TYPE BODY to update the corresponding type body to make it current with the new type definition if necessary.

  3. Upgrade the dependent tables to the latest type version and convert the data in the tables.

    Example 7-4 Upgrading Dependent Tables

  4. Alter dependent PL/SQL program units as needed to take account of changes to the type.

  5. Use OTT or JPUB (or another tool) to generate new header files for applications, depending on whether the application is written in C or Java.

    Adding a new attribute to a supertype also increases the number of attributes in all its subtypes because these inherit the new attribute. Inherited attributes always precede declared (locally defined) attributes, so adding a new attribute to a supertype causes the ordinal position of all declared attributes of any subtype to be incremented by one recursively. The mappings of the altered type must be updated to include the new attributes. OTT and JPUB do this. If you use some other tool, you must be sure that the type headers are properly synchronized with the type definition in the server; otherwise, unpredictable behavior may result.

  6. Modify application code as needed and rebuild the application.

Validating a Type That Has Been Altered

When the system executes an ALTER TYPE statement, it first validates the requested type change syntactically and semantically to make sure it is legal. The system performs the same validations as for a CREATE TYPE statement plus some additional ones. For example, it checks to be sure an attribute being dropped is not used as a partitioning key. If the new spec of the target type or any of its dependent types fails the type validations, the ALTER TYPE statement aborts. No new type version is created, and all dependent objects remain unchanged.

If dependent tables exist, further checking is done to ensure that restrictions relating to the tables and any indexes are observed. Again, if the ALTER TYPE statement fails the check of table-related restrictions, then the type change is aborted, and no new version of the type is created.

When multiple attributes are added in a single ALTER TYPE statement, they are added in the order specified. Multiple type changes can be specified in the same ALTER TYPE statement, but no attribute name or method signature can be specified more than once in the statement. For example, adding and modifying the same attribute in a single statement is not allowed.

For example:


The resulting definition for mytype becomes:

(attr1 NUMBER, attr3 NUMBER, attr4 NUMBER);

The following ALTER TYPE statement, which attempts to make multiple changes to the same attribute (attr5), is invalid:

-- invalid ALTER TYPE statement
ALTER TYPE mytype ADD ATTRIBUTE (attr5 NUMBER, attr6 CHAR(10)),

The following are other notes on validation constraints, table restrictions, and assorted information about the various kinds of changes that can be made to a type.

Dropping an Attribute

  • Dropping all attributes from a root type is not allowed. You must instead drop the type. Because a subtype inherits all the attributes from its supertype, dropping all the attributes from a subtype does not reduce its attribute count to zero; thus, dropping all attributes declared locally in a subtype is allowed.

  • Only an attribute declared locally in the target type can be dropped. You cannot drop an inherited attribute from a subtype. Instead, drop the attribute from the type where it is locally declared.

  • Dropping an attribute which is part of a table partitioning or sub-partitioning key in a table is not allowed.

  • Dropping an attribute of a primary key OID of an object table or an index-organized table (IOT) is not allowed.

  • When an attribute is dropped, the column corresponding to the dropped attribute is dropped.

  • Indexes, statistics, constraints, and any referential integrity constraints referencing a dropped attribute are removed.

Modifying Attribute Type to Increase the Length, Precision, or Scale

  • Expanding the length of an attribute referenced in a function-based index, clustered key or domain index on a dependent table is not allowed.

Dropping a Method

  • You can drop a method only from the type in which the method is defined (or redefined): You cannot drop an inherited method from a subtype, and you cannot drop an redefined method from a supertype.

  • If a method is not redefined, dropping it using the CASCADE option removes the method from the target type and all subtypes. However, if a method is redefined in a subtype, the CASCADE will fail and roll back. For the CASCADE to succeed, you must first drop each redefined method from the subtype that defines it and only then drop the method from the supertype.

    You can consult the USER_DEPENDENCIES table to find all the schema objects, including types, that depend on a given type. You can also run the DBMS_UTILITY.GET_DEPENDENCY utility to find the dependencies of a type.

  • You can use the INVALIDATE option to drop a method that has been redefined, but the redefined versions in the subtypes must still be dropped manually. The subtypes will remain in an invalid state until they are explicitly altered to drop the redefined versions. Until then, an attempt to recompile the subtypes for revalidation will produce the error Method does not override.

    Unlike CASCADE, INVALIDATE bypasses all the type and table checks and simply invalidates all schema objects dependent on the type. The objects are revalidated the next time they are accessed. This option is faster than using CASCADE, but you must be certain that no problems will be encountered revalidating dependent types and tables. Table data cannot be accessed while a table is invalid; if a table cannot be validated, its data remains inaccessible.

    See "If a Type Change Validation Fails".

Modifying the FINAL or INSTANTIABLE Property

  • Altering an object type from INSTANTIABLE to NOT INSTANTIABLE is allowed only if the type has no table dependents.

  • Altering an object type from NOT INSTANTIABLE to INSTANTIABLE is allowed anytime. This change does not affect tables.

  • Altering an object type from NOT FINAL to FINAL is allowed only if the target type has no subtypes.

  • When you alter an object type from FINAL to NOT FINAL or vice versa, you must use CASCADE to convert data in dependent columns and tables immediately. You may not use the CASCADE option NOT INCLUDING TABLE DATA to defer converting data.

    If you alter a type from NOT FINAL to FINAL, you must use CASCADE INCLUDING TABLE DATA. If you alter a type from FINAL to NOT FINAL, you may use either CASCADE INCLUDING TABLE DATA or CASCADE CONVERT TO SUBSTITUTABLE.

    When you alter a type from FINAL to NOT FINAL. the CASCADE option you should choose depends on whether you want to be able to insert new subtypes of the type you are altering in existing columns and tables.

    By default, altering a type from FINAL to NOT FINAL enables you to create new substitutable tables and columns of that type, but it does not automatically make existing columns (or object tables) of that type substitutable. In fact, just the opposite happens: existing columns and tables of the type are marked NOT SUBSTITUTABLE AT ALL LEVELS. If any embedded attribute of such a column is substitutable, an error is generated. New subtypes of the altered type cannot be inserted in such preexisting columns and tables.

    To alter an object type to NOT FINAL in such a way as to make existing columns and tables of the type substitutable (assuming that they are not marked NOT SUBSTITUTABLE), use the CASCADE option CONVERT TO SUBSTITUTABLE. For example:

    Example 7-5 Converting a Type from FINAL to NOT FINAL

        name VARCHAR2(30),
        area NUMBER)

    This CASCADE option marks each existing column as SUBSTITUTABLE AT ALL LEVELS and causes a new, hidden column to be added for the TypeId of instances stored in the column. The column can then store subtype instances of the altered type.

If a Type Change Validation Fails

The INVALIDATE option of the ALTER TYPE statement lets you alter a type without propagating the type change to dependent objects. In this case, the system does not validate the dependent types and tables to ensure that all the ramifications of the type change are legal. Instead, all dependent schema objects are marked invalid. The objects, including types and tables, are revalidated when next referenced. If a type cannot be revalidated, it remains invalid, and any tables referencing it become inaccessible until the problem is corrected.

A table may fail validation because, for example, adding a new attribute to a type has caused the number of columns in the table to exceed the maximum allowable number of 1000, or because an attribute used as a partitioning or clustering key of a table was dropped from a type.

To force a revalidation of a type, users can issue the ALTER TYPE COMPILE statement. To force a revalidation of an invalid table, users can issue the ALTER TABLE UPGRADE statement and specify whether the data is to be converted to the latest type version. (Note that, in a table validation triggered by the system when a table is referenced, table data is always updated to the latest type version: you do not have the option to postpone conversion of the data.)

If a table is unable to convert to the latest type version, then INSERT, UPDATE and DELETE statements on the table are not allowed and its data becomes inaccessible. The following DDLs can be executed on the table, but all other statements which reference an invalid table are not allowed until the table is successfully validated:



All PL/SQL programs containing variables defined using %ROWTYPE of a table or %TYPE of a column or attribute from a table are compiled based on the latest type version. If the table fails the revalidation, then compiling any program units that reference that table will also fail.

ALTER TYPE Statement for Type Evolution

Table 7-1 lists some of the important options in the ALTER TYPE statement for altering the attribute or method definition of a type.

Table 7-1 ALTER TYPE Options for Type Evolution

Option Description
INVALIDATE Invalidates all dependent objects. Using this option bypasses all the type and table checks, to save time.

Use this option only if you are certain that problems will not be encountered validating dependent types and tables. Table data cannot be accessed again until it is validated; if it cannot be validated, it remains inaccessible.

CASCADE Propagates the type change to dependent types and tables. The statement aborts if an error is found in dependent types or tables unless the FORCE option is specified.

If CASCADE is specified with no other options, then the INCLUDING TABLE DATA option for CASCADE is implied, and Oracle converts all table data to the latest type version.

INCLUDING TABLE DATA Converts data stored in all user-defined columns to the most recent version of the column's type.

For each new attribute added to the column's type, a new attribute is added to the data and is initialized to NULL. For each attribute dropped from the referenced type, the corresponding attribute data is removed from each row in the table. All tablespaces containing the table's data must be in read write mode; otherwise, the statement will not succeed.

NOT INCLUDING TABLE DATA Leaves column data as is, associated with the current type version. If an attribute is dropped from a type referenced by a table, then the corresponding column of the dropped attribute is not removed from the table. Only the metadata of the column is marked unused. If the dropped attribute is stored out-of-line (for example, VARRAY, LOB or nested table attribute) then the out-of-line data is not removed. (Unused columns can be removed afterward by using an ALTER TABLE DROP UNUSED COLUMNS statement.)

This option is useful when you have many large tables and may run out of rollback segments if you convert them all in one transaction. This option enables you to convert the data of each dependent table later in a separate transaction (using an ALTER TABLE UPGRADE INCLUDING DATA statement).

Specifying this option will speed up the table upgrade because the table's data is left in the format of the old type version. However, selecting data from this table will require converting the images stored in the column to the latest type version. This is likely to affect performance during subsequent SELECT statements.

Because this option only requires updating the table's metadata all tablespaces are not required to be on-line in read/write mode for the statement to succeed.

FORCE Forces the system to ignore errors from dependent tables and indexes. Errors are logged in a specified exception table so that they can be queried afterward. This option must be used with caution because dependent tables may become inaccessible if some table errors occur.
CONVERT TO SUBSTITUTABLE For use when altering a type from FINAL to NOT FINAL: Converts data stored in all user-defined columns to the most recent version of the column's type and then marks these existing columns and object tables of the type SUBSTITUTABLE AT ALL LEVELS so that they can store any new subtypes of the type that are created.

If the type is altered to NOT FINAL without specifying this option, existing columns and tables of the type are marked NOT SUBSTITUTABLE AT ALL LEVELS, and new subtypes of the type cannot be stored in them. You will be able to store such subtypes only in columns and tables created after the type was altered.

See Also:

Oracle Database SQL Reference for information about ALTER TYPE options

Figure 7-1 graphically summarizes the options for ALTER TYPE INVALIDATE and their effects. In the figure, T1 is a type and T2 is a dependent type. See the notes following the figure.

Figure 7-1 ALTER TYPE Options

Description of adobj029.gif follows
Description of the illustration adobj029.gif

Notes on the figure:

  1. Invalidate: All objects following line (1) are marked invalid

  2. Cascade Not Including Table Data: All objects following line (2) are marked invalid. Metadata of all dependent tables are upgraded to the latest type version, but the table data are not converted.

  3. Cascade Including Table Data: All objects following line (3) are marked invalid. All dependent tables are upgraded to the latest type version, including the table data.

ALTER TABLE Statement for Type Evolution

You can use ALTER TABLE to convert table data to the latest version of referenced types. For an example of converting table data to latest type version, see "Altering a Type by Adding a Nested Table Attribute". See Table 7-1 for a discussion of the INCLUDING DATA option.

See Also:

Oracle Database SQL Reference for information about ALTER TABLE options

The Attribute-Value Constructor

The system-defined attribute value constructor requires you to pass the constructor a value for each attribute of the type. The constructor then sets the attributes of the new object instance to those values, as shown in Example 7-6.

Example 7-6 Setting the Attribute Value with the Constructor

    name VARCHAR2(30),
    area NUMBER);
CREATE TABLE building_blocks of shape;

-- Attribute value constructor: Sets instance attributes to the specified values
INSERT INTO building_blocks
    NEW shape('my_shape', 4));

The keyword NEW preceding a call to a constructor is optional but recommended.

Constructors and Type Evolution

The attribute value constructor function saves you the trouble of defining your own constructors for a type. However, with an attribute-value constructor, you must supply a value for every attribute declared in the type. Otherwise the constructor call will fail to compile.

This requirement of an attribute-value constructor can create a problem if you evolve the type later on—by adding an attribute, for example. When you change the attributes of a type, the type's attribute-value constructor changes, too. If you add an attribute, the updated attribute-value constructor expects a value for the new attribute as well as the old ones. As a result, all the attribute-value constructor calls in your existing code, where values for only the old number of attributes are supplied, will fail to compile.

See "Type Evolution".

Advantages of User-Defined Constructors

User-defined constructors avoid the problem with the attribute-value constructor because user-defined constructors do not need to explicitly set a value for every attribute of a type. A user-defined constructor can have any number of arguments, of any type, and these do not need to map directly to type attributes. In your definition of the constructor, you can initialize the attributes to any appropriate values. Any attributes for which you do not supply values are initialized by the system to NULL.

If you evolve a type—for example, by adding an attribute—calls to user-defined constructors for the type do not need to be changed. User-defined constructors, like ordinary methods, are not automatically modified when the type evolves, so the call signature of a user-defined constructor remains the same. You may, however, need to change the definition of the constructor if you do not want the new attribute to be initialized to NULL.

Defining and Implementing User-Defined Constructors

You define user-defined constructors in the type body, like an ordinary method function. You introduce the declaration and the definition with the phrase CONSTRUCTOR FUNCTION; you must also use the clause RETURN SELF AS RESULT.

A constructor for a type must have the same name as the type. Example 7-7 defines two constructor functions for the shape type. As the example shows, you can overload user-defined constructors by defining multiple versions with different signatures.

Example 7-7 Defining and Implementing User-Defined Constructors

    name VARCHAR2(30),
    area NUMBER,
                               RETURN SELF AS RESULT,
                               area NUMBER) RETURN SELF AS RESULT

                               RETURN SELF AS RESULT IS
        SELF.name := name;
        SELF.area := 0;
                                area NUMBER) RETURN SELF AS RESULT IS
        SELF.name := name;
        SELF.area := area;

A user-defined constructor has an implicit first parameter SELF. Specifying this parameter in the declaration of a user-defined constructor is optional. If you do specify it, its mode must be declared to be IN OUT.

The required clause RETURN SELF AS RESULT ensures that the most specific type of the instance being returned is the same as the most specific type of the SELF argument. In the case of constructors, this is the type for which the constructor is defined.

For example, if the most specific type of the SELF argument on a call to the shape constructor is shape, then this clause ensures that the shape constructor returns an instance of shape (not an instance of a subtype of shape).

When a constructor function is called, the system initializes the attributes of the SELF argument to NULL. Names of attributes subsequently initialized in the function body may be qualified with SELF, as shown in the preceding example, to distinguish them from the names of the arguments of the constructor function, if these are the same. If the argument names are different, no such qualification is necessary. For example:

SELF.name := name;


name := p1;

The function body must include an explicit return; as shown. The return keyword must not be followed by a return expression. The system automatically returns the newly constructed SELF instance.

A user-defined constructor may be implemented in PL/SQL, C, or Java.

Overloading and Hiding Constructors

Like other type methods, user-defined constructors can be overloaded.

User-defined constructors are not inherited, so a user-defined constructor defined in a supertype cannot be hidden in a subtype. However, a user-defined constructor does hide, and thus supersede, the attribute-value constructor for its type if the signature of the user-defined constructor exactly matches the signature of the attribute-value constructor. For the signatures to match, the names and types of the parameters (after the implicit SELF parameter) of the user-defined constructor must be the same as the names and types of the type's attributes. The mode of each of the user-defined constructor's parameters (after the implicit SELF parameter) must be IN.

If an attribute-value constructor is not hidden by a user-defined constructor having the same name and signature, the attribute-value constructor can still be called.

Note that, if you evolve a type—for example, by adding an attribute—the signature of the type's attribute-value constructor changes accordingly. This can cause a formerly hidden attribute-value constructor to become usable again.

Calling User-Defined Constructors

A user-defined constructor is called like any other function. You can use a user-defined constructor anywhere you can use an ordinary function.

The SELF argument is passed in implicitly and may not be passed in explicitly. In other words, usages like the following are not allowed:

NEW constructor(instance, argument_list)

A user-defined constructor cannot occur in the DEFAULT clause of a CREATE or ALTER TABLE statement, but an attribute-value constructor can. The arguments to the attribute-value constructor must not contain references to PL/SQL functions or to other columns, including the pseudocolumns LEVEL, PRIOR, and ROWNUM, or to date constants that are not fully specified. The same is true for check constraint expressions: an attribute-value constructor can be used as part of check constraint expressions while creating or altering a table, but a user-defined constructor cannot.

Parentheses are required in SQL even for constructor calls that have no arguments. In PL/SQL, parentheses are optional when invoking a zero-argument constructor. They do, however, make it more obvious that the constructor call is a function call. The following PL/SQL example omits parentheses in the constructor call to create a new shape:

shape s := NEW my_schema.shape;

The NEW keyword and the schema name are optional.

Example 7-8 creates a subtype under the type created in Example 7-7 and shows examples for calling the user-defined constructors.

Example 7-8 Calling User-Defined Constructors

CREATE TYPE rectangle UNDER shape (
    len NUMBER,
    wth NUMBER,
        name VARCHAR2, side NUMBER) RETURN SELF as RESULT);
        SELF.name := name;
        SELF.area := len*wth;
        SELF.len := len;
        SELF.wth := wth;
        RETURN ;
        SELF.name := name;
        SELF.area := side * side;
        SELF.len := side;
        SELF.wth := side;
        RETURN ;

CREATE TABLE shape_table OF shape;
INSERT INTO shape_table VALUES(shape('shape1')); 
INSERT INTO shape_table VALUES(shape('shape2', 20)); 
INSERT INTO shape_table VALUES(rectangle('rectangle', 2, 5)); 
INSERT INTO shape_table VALUES(rectangle('quadrangle', 12, 3));
INSERT INTO shape_table VALUES(rectangle('square', 12));

The following query selects the rows in the shape_table:

SELECT VALUE(s) FROM shape_table s;

SHAPE('shape1', 0)
SHAPE('shape2', 20)
RECTANGLE('rectangle', 10, 2, 5)
RECTANGLE('quadrangle', 36, 12, 3)
RECTANGLE('square', 144, 12, 12)

The following PL/SQL code calls the constructor:

s shape := NEW shape('void');

Constructors for SQLJ Object Types

A SQLJ object type is a SQL object type mapped to a Java class. A SQLJ object type has an attribute-value constructor. It can also have user-defined constructors that are mapped to constructors in the referenced Java class.

Example 7-9 Creating a SQLJ Object

   EXTERNAL NAME 'university.address' LANGUAGE JAVA
     street   VARCHAR2(100) EXTERNAL NAME 'street',
     city     VARCHAR2(50)  EXTERNAL NAME 'city',
     state    VARCHAR2(50)  EXTERNAL NAME 'state',
     zipcode  NUMBER        EXTERNAL NAME 'zipcode',
                                  city VARCHAR2, state VARCHAR2, zipcode NUMBER)
      NAME  'university.address (java.lang.String, java.lang.String,
                      java.lang.String, int) return address');

A SQLJ type of a serialized representation can have only a user-defined constructor. The internal representation of an object of SQLJ type is opaque to SQL, so an attribute-value constructor is not possible for a SQLJ type.

Transient and Generic Types

Oracle has three special SQL datatypes that enable you to dynamically encapsulate and access type descriptions, data instances, and sets of data instances of any other SQL type, including object and collection types. You can also use these three special types to create anonymous types, including anonymous collection types.

The three SQL types are implemented as opaque types. In other words, the internal structure of these types is not known to the database; their data can be queried only by implementing functions (typically 3GL routines) for the purpose. Oracle provides both an OCI and a PL/SQL API for implementing such functions.

The three generic SQL types are described in Table 7-2.

Table 7-2 Generic SQL Types

Type Description
SYS.ANYTYPE A type description type. A SYS.ANYTYPE can contain a type description of any SQL type, named or unnamed, including object types and collection types.

An ANYTYPE can contain a type description of a persistent type, but an ANYTYPE itself is transient: in other words, the value in an ANYTYPE itself is not automatically stored in the database. To create a persistent type, use a CREATE TYPE statement from SQL.

SYS.ANYDATA A self-describing data instance type. A SYS.ANYDATA contains an instance of a given type, with data, plus a description of the type. In this sense, a SYS.ANYDATA is self-describing. An ANYDATA can be persistently stored in the database.
SYS.ANYDATASET A self-describing data set type. A SYS.ANYDATASET type contains a description of a given type plus a set of data instances of that type. An ANYDATASET can be persistently stored in the database.

Each of these three types can be used with any built-in type native to the database as well as with object types and collection types, both named and unnamed. The types provide a generic way to work dynamically with type descriptions, lone instances, and sets of instances of other types. Using the APIs, you can create a transient ANYTYPE description of any kind of type. Similarly, you can create or convert (cast) a data value of any SQL type to an ANYDATA and can convert an ANYDATA (back) to a SQL type. And similarly again with sets of values and ANYDATASET.

The generic types simplify working with stored procedures. You can use the generic types to encapsulate descriptions and data of standard types and pass the encapsulated information into parameters of the generic types. In the body of the procedure, you can detail how to handle the encapsulated data and type descriptions of whatever type.

You can also store encapsulated data of a variety of underlying types in one table column of type ANYDATA or ANYDATASET. For example, you can use ANYDATA with Advanced Queuing to model queues of heterogeneous types of data. You can query the data of the underlying datatypes like any other data.

Example 7-10 defines and executes a PL/SQL procedure that uses methods built into SYS.ANYDATA to access information about data stored in a SYS.ANYDATA table column.

Example 7-10 Using SYS.ANYDATA

    ownerno NUMBER, ownername VARCHAR2(10) );
    breed VARCHAR2(10), dogname VARCHAR2(10) );
INSERT INTO mytab VALUES ( 1, SYS.ANYDATA.ConvertNumber (5) );
INSERT INTO mytab VALUES ( 2, SYS.ANYDATA.ConvertObject (
    dogowner ( 5555, 'John') ) );

  CURSOR cur IS SELECT id, data FROM mytab;

  v_id mytab.id%TYPE;
  v_data mytab.data%TYPE;
  v_type SYS.ANYTYPE;
  v_typecode PLS_INTEGER;
  v_typename VARCHAR2(60);
  v_dummy PLS_INTEGER;
  v_n NUMBER;
  v_dogowner dogowner;
  non_null_anytype_for_NUMBER exception;
  unknown_typename exception;

  OPEN cur;
      FETCH cur INTO v_id, v_data;
      v_typecode := v_data.GetType ( v_type /* OUT */ );
      CASE v_typecode
        WHEN Dbms_Types.Typecode_NUMBER THEN
          IF v_type IS NOT NULL
            THEN RAISE non_null_anytype_for_NUMBER; END IF;
          v_dummy := v_data.GetNUMBER ( v_n /* OUT */ );
          Dbms_Output.Put_Line (
            To_Char(v_id) || ': NUMBER = ' || To_Char(v_n) );
        WHEN Dbms_Types.Typecode_Object THEN
          v_typename := v_data.GetTypeName();
          IF v_typename NOT IN ( 'HR.DOGOWNER' )
            THEN RAISE unknown_typename; END IF;
          v_dummy := v_data.GetObject ( v_dogowner /* OUT */ );
          Dbms_Output.Put_Line (
            To_Char(v_id) || ': user-defined type = ' || v_typename ||
            '(' || v_dogowner.ownerno || ', ' || v_dogowner.ownername || ' )' );
      END CASE;
    CLOSE cur;

  WHEN non_null_anytype_for_NUMBER THEN
      RAISE_Application_Error ( -20000,
        'Paradox: the return AnyType instance FROM GetType ' ||
        'should be NULL for all but user-defined types' );
  WHEN unknown_typename THEN
      RAISE_Application_Error ( -20000,
        'Unknown user-defined type ' || v_typename ||
        ' - program written to handle only HR.DOGOWNER' );

SELECT t.data.gettypename() FROM mytab t;

The query and the procedure P in the preceding code sample produce output like the following:


1: NUMBER = 5
2: user-defined type = HR.DOGOWNER(5555, John )

Corresponding to the three generic SQL types are three OCI types that model them. Each has a set of functions for creating and accessing the respective type:

  • OCIType, corresponding to SYS.ANYTYPE

  • OCIAnyData, corresponding to SYS.ANYDATA

  • OCIAnyDataSet, corresponding to SYS.ANYDATASET

    See Also:

User-Defined Aggregate Functions

Oracle provides a number of pre-defined aggregate functions such as MAX, MIN, SUM for performing operations on a set of records. These pre-defined aggregate functions can be used only with scalar data. However, you can create your own custom implementations of these functions, or define entirely new aggregate functions, to use with complex data—for example, with multimedia data stored using object types, opaque types, and LOBs.

User-defined aggregate functions are used in SQL DML statements just like Oracle's own built-in aggregates. Once such functions are registered with the server, Oracle simply invokes the aggregation routines that you supplied instead of the native ones.

User-defined aggregates can be used with scalar data as well. For example, it may be worthwhile to implement special aggregate functions for working with complex statistical data associated with financial or scientific applications.

User-defined aggregates are a feature of the Extensibility Framework. You implement them using ODCIAggregate interface routines.

See Also:

Oracle Database Data Cartridge Developer's Guide for information on using the ODCIAggregate interface routines to implement user-defined aggregate functions

Partitioning Tables That Contain Oracle Objects

Partitioning addresses the key problem of supporting very large tables and indexes by allowing you to decompose them into smaller and more manageable pieces called partitions. Oracle extends partitioning capabilities by letting you partition tables that contain objects, REFs, varrays, and nested tables. Varrays stored in LOBs are equipartitioned in a way similar to LOBs. See also Oracle Database Application Developer's Guide - Large Objects.

Example 7-11 partitions the purchase order table along zip codes (ToZip), which is an attribute of the ShipToAddr embedded column object. For the purposes of this example, the LineItemList nested table was made a varray to illustrate storage for the partitioned varray.


Nested tables are allowed in tables that are partitioned; however, the storage table associated with the nested table is not partitioned.

Example 7-11 Partitioning a Table That Contains Objects

CREATE TYPE LineItemList_vartyp as varray(10000) of LineItem_objtyp;
CREATE TYPE PurchaseOrder_typ AS OBJECT ( 
      PONo                NUMBER, 
      Cust_ref            REF Customer_objtyp, 
      OrderDate           DATE, 
      ShipDate            DATE, 
      OrderForm           BLOB, 
      LineItemList        LineItemList_vartyp, 
      ShipToAddr          Address_objtyp, 
      ret_value RETURN NUMBER, 
      total_value RETURN NUMBER);
CREATE TABLE PurchaseOrders_tab of PurchaseOrder_typ  
    LOB (OrderForm) store as (nocache logging)  
    PARTITION BY RANGE (ShipToAddr.zip)  
      (PARTITION PurOrderZone1_part  
         VALUES LESS THAN ('59999')  
         LOB (OrderForm) store as (  
               storage (INITIAL 10 MINEXTENTS 10 MAXEXTENTS 100))  
         VARRAY LineItemList store as LOB (  
               storage (INITIAL 10 MINEXTENTS 10 MAXEXTENTS 100)),  
    PARTITION PurOrderZone6_part  
         VALUES LESS THAN ('79999')  
         LOB (OrderForm) store as (  
               storage (INITIAL 10 MINEXTENTS 10 MAXEXTENTS 100))  
         VARRAY LineItemList store as LOB (  
               storage (INITIAL 10 MINEXTENTS 10 MAXEXTENTS 100)),  
    PARTITION PurOrderZoneO_part  
       VALUES LESS THAN ('99999')  
        LOB (OrderForm) store as ( 
               storage (INITIAL 10 MINEXTENTS 10 MAXEXTENTS 100))  
        VARRAY LineItemList store as LOB (  
               storage (INITIAL 10 MINEXTENTS 10 MAXEXTENTS 100)));

How Locators Improve the Performance of Nested Tables

Collection types do not map directly to a native type or structure in languages such as C++ and Java. An application using those languages must access the contents of a collection through Oracle interfaces, such as OCI.

Generally, when the client accesses a nested table explicitly or implicitly (by fetching the containing object), Oracle returns the entire collection value to the client process. For performance reasons, a client may wish to delay or avoid retrieving the entire contents of the collection. Oracle handles this case for you by using a locator instead of the real nested table value. When you really access the contents of the collection, they are automatically transferred to the client.

A nested table locator is like a handle to the collection value. It attempts to preserve the value or copy semantics of the nested table by containing the database snapshot as of its time of retrieval. The snapshot helps the database retrieve the correct instantiation of the nested table value at a later time when the collection elements are fetched using the locator. The locator is scoped to a session and cannot be used across sessions. Because database snapshots are used, it is possible to get a snapshot too old error if there is a high update rate on the nested table. Unlike a LOB locator, the nested table locator is truly a locator and cannot be used to modify the collection instance.