Tables of Final Object Types

Object tables have an object identifier that uniquely identifies every row in the table. The following situations can occur:

• If there is no need to unload and reload the object identifier, then the external table only needs to contain fields for the attributes of the type for the object table.

• If the object identifier (OID) needs to be unloaded and reloaded and the OID for the table is one or more fields in the table, (also known as primary-key-based OIDs), then the external table has one column for every attribute of the type for the table.

• If the OID needs to be unloaded and the OID for the table is system-generated, then the procedure is more complicated. In addition to the attributes of the type, another column needs to be created to hold the system-generated OID.

The steps in the following example demonstrate this last situation.

1. Create a table of a type with system-generated OIDs:

   SQL> CREATE TYPE person AS OBJECT (name varchar2(20)) NOT FINAL
     2  /
   
   Type created.
   
   SQL> CREATE TABLE people OF person;
   
   Table created.
   
   SQL> INSERT INTO people VALUES ('Euclid');
   
   1 row created.
   

2. Create an external table in which the column OID is used to hold the column containing the system-generated OID.

   SQL> CREATE TABLE people_xt
     2  ORGANIZATION EXTERNAL
     3  (
     4    TYPE ORACLE_DATAPUMP
     5    DEFAULT DIRECTORY def_dir1
     6    LOCATION ('people.dmp')
     7  )
     8  AS SELECT SYS_NC_OID$ oid, name FROM people;
   
   Table created.
   

3. Create another table of the same type with system-generated OIDs. Then, execute an INSERT statement to load the new table with data unloaded from the old table.

   SQL> CREATE TABLE people2 OF person;
   
   Table created.
   
   SQL> 
   SQL> INSERT INTO people2 (SYS_NC_OID$, SYS_NC_ROWINFO$)
     2  SELECT oid, person(name) FROM people_xt;
   
   1 row created.
   
   SQL> 
   SQL> SELECT SYS_NC_OID$, name FROM people
     2  MINUS
     3  SELECT SYS_NC_OID$, name FROM people2;
   
   no rows selected