SQL*Loader is a fast and easy way to get data into a
database without writing long, complicated scripts. However, when it comes to
Oracle objects that are related to each other, things can get complicated, and
the documentation is a little vague on the subject. I’ll present two scenarios
in this tip: one with system-generated Oracle Internet Directories (OIDs) and another with user-defined OIDs.

First, let’s start with simple objects that describe a
genealogical relationship between three people.
View Listing A. If we want to “export” the data into a text file
with SQL*Loader, we need to export the system-generated OIDs
along with the data. The documentation doesn’t tell you that you can extract
the OIDs using the hidden column SYS_NC_OID$.
Selecting this column from an object table will return the 16-byte RAW OID,
which automatically converts into a 32-character hex string by SQL*Plus. You
can use this column in comma-delimited SQL*Loader files to re-create the
objects and their correct relationships.

View Listing B. The column “sysid” is a
dummy column that contains the object’s OID. The SQL*Loader control file syntax
“oid(sysid)” takes this column and applies it to the object as it’s created.
View Listing C, in which references are resolved by taking the dummy fields “groom_id” and “bride_id” and constructing references to the PEOPLE table. This is the equivalent to saying make_ref(people,hextoraw(:oid)) from a SQL INSERT statement.

There is a major problem with using the SYS_NC_OID$ column.
Not only is it a barely documented hidden column, but there are numerous times
in which the documentation clearly states that developers should not rely on
this datatype or value as it may change in future
versions of the database.

It seems stable enough to me. If you export a table with
objects, you’ll see that the exported SQL is explicitly inserting data into
this column during the import. If this column changes, wouldn’t export files
become invalid? What would be better is to have an “export OID to standard
SQL type” function but, as of the latest version, this function isn’t
available. This creates a severe limitation on external programs that wish to
store a generic reference to an object outside the “approved”
programming languages (PL/SQL, Java, OCI, and OO4O). Instead, a developer would
have to rely on primary keys and user-defined OIDs to
pass references.

Let’s use a simple integer sequence for our user-defined
OID. A user-defined OID can be any datatype, as long
as it’s unique within a table, and preferably globally unique. You can use SYS_GUID() to generate documented globally unique id’s, but
integers work just as well and are more readable here. Note that references to
user-defined OID’s must be “scoped.” If you
don’t scope a reference, it will refer to the system-generated OID of an
object. You’ll get an ORA-22979 if you try to reference a user-defined OID with
a non-scoped reference, and the documentation isn’t clear about this requirement.

Listing D
contains the new table definitions for the user-defined OID.
Listing E
includes the control files updated to reference objects with our OID’s instead of waiting for the database to generate them
for us.

TechRepublic’s Oracle newsletter covers automating Oracle utilities, generating database alerts, solving directed graph problems, and more. Automatically subscribe today!