Java Programming with Oracle SQLJ

4.5. Object Identifiers and REF Columns

Every row in an object table is assigned a unique object identifier (OID), which is a hexadecimal number generated by Oracle. An object reference, represented using an Oracle REF type, can store an OID and is used as a pointer to a row in an object table. An object reference is similar to a pointer in the C programming language and is the object table version of a foreign key.

In this section, you will learn how to retrieve the OID for row objects, how to define a column in a table using the REF type, how to populate and modify a REF column, and how to dereference a REF column. Then, I describe REF custom class files and how to access REF columns using SQLJ. Finally, this section contains a complete SQLJ program that illustrates how to access REF columns.

To demonstrate the use of OIDs and REF columns, I guide you through creating and populating an object table, named products, that will be used to store product details. I show you how to retrieve the OID for a row in the products table, and how to create a table, named purchases, that will store OIDs from the products and customers2 tables. This purchases table will be used to track a customer's purchases.

You don't need to type any of the DDL or DML statements shown in this section: they are contained in the object_user_schema.sql script, which you or your DBA should have run already.

4.5.1. Create and Populate the Products Table

First, you need an object type and table to hold product ...