Assigning an OID to an Object Type

The CREATE TYPE statement has an optional keyword OID, which associates a user-specified object identifier (OID) with the type definition. This feature was available effective with release 8.0.3; however, it was not documented because it is intended for use primarily by Oracle product developers and by developers of data cartridges. However, it should be used by anyone who creates an object type that will be used in more than one database.

Each type has an OID. If you create an object type and do not specify an OID, Oracle generates an OID and assigns it to the type. Oracle8i uses the OID internally for operations pertaining to that type. Using the same OID for a type is important if you plan to share instances of the type across databases for such operations as export/import and distributed queries.

Note: Most other Oracle8i documentation refers to the use of OIDs with rows in object tables. In CREATE TYPE with OID, an OID is assigned to the type itself. Of course, each row created in a table with a column of the specified type will also still have a row-specific OID.

For example, assume that you want to create a type named SpecialPerson and then instantiate that type in two different databases with tables named SpecialPersonTable1 and SpecialPersonTable2. The RDBMS needs to know that the SpecialPerson type is the same type in both instances, and therefore the type must be defined using the same OID in both databases. If you do not specify an OID with CREATE TYPE, a unique identifier is created automatically by the RDBMS.

The syntax for specifying an OID for an object type is as follows:

CREATE OR REPLACE TYPE type_name OID 'oid' AS OBJECT (attribute datatype 
[,...]);

In the following example, the SELECT statement generates an OID, and the CREATE TYPE statement uses the OID in creating an object type named mytype. Be sure to use the SELECT statement to generate a different OID for each object type to be created, because this is the only way to guarantee that each OID is valid and globally unique.

SVRMGR> SELECT SYS_OP_GUID() FROM DUAL; 
SYS_OP_GUID()                    
-------------------------------- 
19A57209ECB73F91E03400400B40BBE3 
1 row selected. 
 
SVRMGR> CREATE TYPE mytype OID '19A57209ECB73F91E03400400B40BBE3'
     2> AS OBJECT (attrib1 NUMBER); 
Statement processed.