Retrieve the actual objects stored in an object reference using the DEREF() function,

CREATE TYPE t_address AS OBJECT (
    street VARCHAR2(15),
    city VARCHAR2(15),
    state CHAR(2),
    zip VARCHAR2(5)
);
/

CREATE TYPE t_person AS OBJECT (
    id INTEGER,
    first_name VARCHAR2(10),
    last_name VARCHAR2(10),
    dob DATE,
    phone VARCHAR2(12),
    address t_address
);
/


CREATE TABLE emp OF t_person;

SQL> INSERT INTO emp VALUES (
  2       t_person(1, 'Jason', 'Brown', '01-FEB-1995', '999-555-9999',t_address(
'Main Street', 'Smalltown', 'CA', '12345'))
  3  );

1 row created.

SQL>
CREATE TABLE my_ref (
   id INTEGER PRIMARY KEY,
   e_ref REF t_person SCOPE IS emp
);


INSERT INTO my_ref (id,e_ref) VALUES (1,(SELECT REF(e) FROM emp e WHERE e.id = 1));

SELECT * FROM my_ref;

SQL> SELECT DEREF(e_ref) FROM my_ref;

DEREF(E_REF)(ID, FIRST_NAME, LAST_NAME, DOB, PHONE, ADDRESS(STREET, CITY, STATE,
--------------------------------------------------------------------------------
SQL>

SQL> SELECT DEREF(e_ref).first_name FROM my_ref;

DEREF(E_RE
----------


SQL>

SQL> UPDATE my_ref SET e_ref = (SELECT REF(e) FROM emp e WHERE e.id = 2) WHERE id = 1;

1 row updated.

SQL>

SELECT DEREF(e_ref) FROM my_ref;