Oracle Database Tips by Donald Burleson

The Concept of Object Identifiers (OID) in Oracle9i

Oracle8 introduced the concept of objects as they apply to Oracle. Each object in Oracle8, Oracle8i, and Oracle9i (i.e., table, cluster, etc.) has a 16-byte object identifier. This object identifier (OID) is guaranteed to be globally unique across all databases in your environment. It is a 16-byte, base-64 number that allows for a ridiculously high number of objects to be identified (in the peta-region of countability—a quadrillion? The maximum is 2**128 (340,283,266,920,938,463,463,374,607,431,768,211,456)).

The OID, as well as being a globally unique identifier, is used to construct REFs for nested tables. In some statements involving nested tables, if you don't specifically tell Oracle to bring back the UNREF value (i.e., translate the OID and get the data), you will get a 42- to 46-byte REF number, as useful as that sounds. The number itself is simply an identifier and contains no "intelligence" such as would be in a rowid. The REF value can vary in size between 42 and 46 bytes of internal storage.

Oracle9i Object Types

Before we can begin to discuss objects in Oracle8, Oracle8i, and Oracle9i, we have to address object types. As their name implies, an object type is used to define an object. To bridge the gulf between Oracle7 and Oracle9i, you can think of an object type as a predefined row that you can then use to build Oracle9i objects. The exact format for creating an object table is shown in the SQL reference in the documentation area of the http://technet.oracle.com/ site link on the Wiley Web site. The examples that follow demonstrate how to use Oracle types to create an object table.

Before you can build an object table in Oracle8, 8i or 9i, you must define its types. A table can consist of single columns, types, or a mix, as well as varrays (which are discussed later in this chapter). There are only object TYPEs. Under Oracle8i, the AUTHID clause was added to the CREATE TYPE command. In Oracle9i, the CREATE TYPE command has been extended with the clauses required to support inheritance. Also in Oracle9i, type bodies support overloading, thus providing polymorphism.  Let's look at a simple type definition and how it is used to build an Oracle9i object table. The only instances the AS OBJECT clause is not used is with varray, TABLE, or incomplete type specifications.

See Code Depot

www.dba-oracle.com/oracle_scripts.htm