Replacing an Oracle Object Type Column with a REF to a Table of that Type

The following example illustrates an alternative to using an Oracle Object datatype as a column in a table. In a table definition, you can replace a line that identifies a column as an Oracle Object datatype with a REF to a table that is of the Oracle Object datatype. That is, assume you have a table definition with a column that is an Oracle Object Type. Create a second table with the same name and type as the Oracle Object. You can then redefine the column in the original table as a REF to the second table. This powerful technique gives you certain advantages:

Each table can be translated to a separate entity object.
The REF between the tables is translated to an association between the entity objects, one of which represents the Oracle Object Type.
You can create separate entity-level validations on the entity object representing the table and the entity object representing the Oracle Object type.
You can make the entity object representing the Oracle Object type read-only and allow the entity object representing the table to be updateable. The REF (or association) will allow you to essentially have a read-only attribute in an entity object that is otherwise updateable.
After you create the entity objects for the table and the Oracle Object type, use the Association Wizard to set the Composition flag. The parent entity object will not be able to be deleted before its children are deleted.

The following example demonstrates the SQL code needed to make use of this technique. The example begins with the definition of the address_objtyp as an Oracle Object type. It then creates a table named address_objtab of type address_objtyp. The definition of the table people_reltab2 has already been rewritten to replace the line:

address  address_objtyp

with the REF statement:

address_ref REF address_objtyp
   SCOPE IS address_objtab ) -- REF specified

The SQL code concludes with an example of inserting data into the tables.

CREATE TYPE address_objtyp AS OBJECT (
  street VARCHAR2(200),
  city VARCHAR2(200),
  state CHAR(2),
  zipcode VARCHAR2(20))
  /

CREATE TABLE address_objtab OF address_objtyp ;
/

CREATE TABLE people_reltab2 (
 id NUMBER(4)
 CONSTRAINT pk_people_reltab2 PRIMARY KEY,
 name VARCHAR(15),
 Phone Number(6),
 address_ref REF address_objtyp
 SCOPE IS address_objtab ) -- REF specified
  /

Insert into address_objtab values(address_objtyp
 ('St1','FosterCity', 'CA', '94491'))
 Insert into address_objtab values
 (address_objtyp('St2','Redwood', 'CA', '94331'))
  insert into people_reltab2 values( 1, 'Sanjay',911,
  (SELECT REF(p) FROM
  address_objtab p WHERE street = 'St1'));
  insert into people_reltab2 values( 2, 'KING',1241,
  (SELECT REF(p) FROM
  address_objtab p WHERE street = 'St2'));