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
CREATE OR REPLACE TYPE [schema.]type_name
AS OBJECT (element_list)
The element_list can consist of:
* Attribute_name Datatype-constraint pairs
* User-defined types
* Method declarations with pragma
* 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
NUMBER (precision, scale)
The following datatypes are provided for
compatibility but internally are treated the same as NUMBER:
The following are large object datatypes:
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
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]
OF [schema.]object_type [(object_properties)]
[[NOT] SUBSTITUTABLE AT ALL LEVELS]
[ON COMMIT DELETE|PRESERVE ROWS]
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
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.