Slide 12.11: Object references (cont.)

Object References (Cont.)

Three Operators

REF, to take the REF of a row in an object table.
REF INTO, to capture the REF to a new row in an object table while inserting.
DEREF, to return an object value identified by a reference.

SQL> SET SERVEROUTPUT ON; 

SQL> DECLARE 
  2    v_person           person_typ; 
  3    v_person_ref  REF  person_typ; 
  4  BEGIN 
  5    SELECT  REF( p )  INTO  v_person_ref 
  6      FROM  persons p  WHERE  p.ssn = 123456789; 
  7    SELECT  DEREF( v_person_ref )  INTO  v_person  FROM  dual; 
  8    DBMS_OUTPUT.PUT_LINE ( v_person.name ); 
  9  END; 
 10  / 

PowerPuff Girls
PL/SQL procedure successfully completed.

DBMS_OUTPUT package enables you to display output from PL/SQL blocks. The procedure PUT_LINE outputs information to a buffer in the SGA (System Global Area), a memory region within main memory used to store data for fast access. You display the information by calling the procedure GET_LINE or by setting SERVEROUTPUT ON in SQL*Plus.

DUAL is a table automatically created by Oracle along with the data dictionary. Selecting from the DUAL table is useful for computing a constant expression with the SELECT command.