Using SQL*Loader to load objects
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.
Scott Stephens worked for Oracle for more than 13 years in technical support, e-commerce, marketing, and software development.