Call now: 252-767-6166  
Oracle Training Oracle Support Development Oracle Apps

 E-mail Us
 Oracle Articles
New Oracle Articles

 Oracle Training
 Oracle Tips

 Oracle Forum
 Class Catalog

 Remote DBA
 Oracle Tuning
 Emergency 911
 RAC Support
 Apps Support
 Oracle Support

 SQL Tuning

 Oracle UNIX
 Oracle Linux
 Remote s
 Remote plans
 Application Server

 Oracle Forms
 Oracle Portal
 App Upgrades
 SQL Server
 Oracle Concepts
 Software Support

 Remote S


 Consulting Staff
 Consulting Prices
 Help Wanted!


 Oracle Posters
 Oracle Books

 Oracle Scripts

Don Burleson Blog 









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
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
   NUMBER (precision, scale)

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

   NUMERIC(precision, scale)
   DECIMAL(precision, scale)
   DEC(precision, scale)

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

OF [schema.]object_type [(object_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".


Oracle Training at Sea
oracle dba poster

Follow us on Twitter 
Oracle performance tuning software 
Oracle Linux poster


Burleson is the American Team

Note: This Oracle documentation was created as a support and Oracle training reference for use by our DBA performance tuning consulting professionals.  Feel free to ask questions on our Oracle forum.

Verify experience! Anyone considering using the services of an Oracle support expert should independently investigate their credentials and experience, and not rely on advertisements and self-proclaimed expertise. All legitimate Oracle experts publish their Oracle qualifications.

Errata?  Oracle technology is changing and we strive to update our BC Oracle support information.  If you find an error or have a suggestion for improving our content, we would appreciate your feedback.  Just  e-mail:  

and include the URL for the page.


Burleson Consulting

The Oracle of Database Support

Oracle Performance Tuning

Remote DBA Services


Copyright © 1996 -  2020

All rights reserved by Burleson

Oracle ® is the registered trademark of Oracle Corporation.