Object References

Object reference (REF) is a system-generated value that locates a persistent object, which is an object that continues to exist and retains its data beyond the duration of the process that creates it. 
SQL> CREATE TYPE  acc_typ  AS OBJECT ( 
  2    no          INTEGER, 
  3    acc_holder  REF  person_typ ); 

Type created.

SQL> CREATE TABLE  accounts  OF  acc_typ ( 
  2    SCOPE FOR ( acc_holder ) IS   persons  ); 

Table created.

SQL> INSERT INTO  accounts 
  2    SELECT  1001,  REF(p)  FROM  persons p 
  3      WHERE  p.ssn = 123456789; 

1 row created.
  • SCOPE FOR table_name restricts the scope of the column REF values to table_name. The REF values for the column must come from REF values obtained from the object table specified in the clause.

  • Typically, a REF is comprised of the target object’s OID, an object table identifier, and a database identifier. 
      SQL> SELECT  acc_holder  FROM  accounts; 
   
   ACC_HOLDER
   --------------------------------------------------------------------------------
   0000220208D494551C0EDD339DE034080020C47386D494551C0EDB339DE034080020C47386



      What do you call a dog with no legs?    
      It doesn’t matter,    
      it’s not going to come anyway.