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>
Retrieves the customer's first_name and address.street attributes, plus the product's name attribute:
SQL> SELECT DEREF(e_ref).first_name FROM my_ref;
DEREF(E_RE
----------
SQL>
The following UPDATE statement modifies the e_ref column:
SQL> UPDATE my_ref SET e_ref = (SELECT REF(e) FROM emp e WHERE e.id = 2) WHERE id = 1;
1 row updated.
SQL>
The following query verifies this change:
SELECT DEREF(e_ref) FROM my_ref;
Home »
Oracle »
PL/SQL »
Oracle »
PL/SQL »
Object Types:
- Creating Object Types
- A type with member function:
- Using DESCRIBE to Get Information on Object Types
- Using Object Types in Database Tables
- Retrieve an individual column object from a table
- Call method from type
- UPDATE/DELETE row based on custom data type
- Object Tables
- VALUE() selects a row from an object table.
- UPDATE Object Table
- DELETE rows from Object Table
- Object table abased on nested types
- Object Identifiers and Object References
- REF type for an object reference
- Retrieve the actual objects stored in an object reference using the DEREF() function,
- Comparing Object Values
Related: