Object Types

Oracle Database Tips by Donald Burleson

If you will be using the type to build an object table that will be REFed by a second table, it must be constructed as an OBJECT type and thus include an object ID (OID). Nested tables and varrays are limited, in that they cannot themselves store a nested table or varray; a second OBJECT table is not limited. In cases where the entity relationship diagram (ERD) shows a series of one-to-many type relationships, OBJECT tables will have to be used to implement this relationship structure under the object-oriented paradigm in Oracle8.

The basic command to create an OBJECT type would be:

CREATE OR REPLACE TYPE [schema.]type_name
AUTHID CURRENT_USER|DEFINER
AS OBJECT (element_list)

The element_list can consist of:

* Attribute_name Datatype-constraint pairs

* User-defined types

* Method declarations with pragma specification

* MAP or ORDER method specifications

I will refer the reader to the Web documentation in the Oracle technet site for the complete syntax.

Note: Object types can be used in partition tables only after release 8i.

The possible datatype specifications for a type are:

   REF schema.object_type_name
   schema.type_name
   VARCHAR2(size)
   NUMBER (precision, scale)
   DATE
   RAW(size)
   CHAR(size)
   CHARACTER(size)
   CHAR(size)
   CHARACTER VARYING(size)
   CHAR VARYING(size)
   VARCHAR(size)

The following datatypes are provided for compatibility but internally are treated the same as NUMBER:

   NUMERIC(precision, scale)
   DECIMAL(precision, scale)
   DEC(precision, scale)
   INTEGER
   INT
   SMALLINT
   FLOAT(size)
   DOUBLE PRECISION
   REAL

The following are large object datatypes:

   BLOB
   CLOB
   BFILE

Tip: The NCLOB datatype is also a LOB, but it cannot be used for TYPE definitions.

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

You can define either the MAP or ORDER method in a type specification, but not both. If a MAP or an ORDER method is not specified, only comparisons for equality or inequality can be performed, thus the object instances cannot be ordered. No comparison method needs to be specified to determine the equality of two object types. 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. No comparison method needs to be specified to determine the equality of two object types.

Creation of Object Tables

Object tables differ from relational tables in that an object table has an object identifier that is system-generated and -maintained.

Object Table CREATE Command Definition

Oracle8, Oracle8i, and Oracle9i allow the creation of OBJECT tables as well as relational tables. An object table is made up of object types or a combination of standard and object types. Prior to Oracle8i, an object table could not be partitioned. Object tables have OIDs and can be used for a REF call. To use a standard nonobject relational table in a REF, it must be masked with an object view.

The details of the command to create an object table are contained in the SQL reference in the Web documentation on the Oracle Technet site.

CREATE [GLOBAL TEMPORARY] TABLE [schema.]table_name
OF [schema.]object_type [(object_properties)]
[[NOT] SUBSTITUTABLE AT ALL LEVELS]
[ON COMMIT DELETE|PRESERVE ROWS]
[OID_clause][OID_index_clause][physical_properties]
[table_properties];

The object_properties clause includes column and attribute information, as well as default value, constraint, and table constraints, along with any REF information. A REF is similar to a foreign key reference in straight relational tables, but instead of holding an actual value used to tie back to the parent table, it holds a pointer (in the form of an OID) to the actual row in the parent table.

The [[NOT] SUBSTITUTABLE AT ALL LEVELS] clause indicates that the object table types are or are not substitutable at all levels. If a column is substitutable, it means that if a TYPE is a subtype or is somewhere in a chain of subtypes, any type or supertype for which this type is a subtype can be inserted to its place in the table simply by calling the supertype or type's constructor rather than the subtype constructor. For example, assume we have the PERSON_T - EMPLOYEE_T - PARTTIME_EMP_T type hierarchy, where PERSON_T is the main type, EMPLOYEE_T is a subtype of PERSON_T, and PARTTIME_EMP_T is a subtype of the EMPLOYEE_T type. This would indicate that PARTTIME_PERSON_T has its own attributes, plus all those in EMPLOYEE_T, including any the EMPLOYEE_T inherited from PERSON_T. If a table were defined using the PARTTIME_EMP_T type, you could also use the EMPLOYEE_T or PERSON_T constructor methods to insert a subset of data to the same column location. Using NOT SUBSTITUTABLE turns off this capability.

The ON COMMIT clause is used only if the object table is a GLOBAL TEMPORARY object table.

The OID_clause is used to tell Oracle whether or not the OID is SYSTEM GENERATED (the default) or the PRIMARY KEY. The OID_index_clause specifies how the OID is to be indexed, and specifies the storage parameters for the index.

The physical_properties and table_properties clauses are the same as for a relational table.

This is an excerpt from Mike Ault, bestselling author of "Oracle 10g Grid and Real Application Clusters".

��