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
      3  (
      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.
      2  SELECT oid, person(name) FROM people_xt;
    1 row created.
    SQL> SELECT SYS_NC_OID$, name FROM people
      2  MINUS
      3  SELECT SYS_NC_OID$, name FROM people2;
    no rows selected