Write a query using a DEREF data type to retrieve related data.
Writing Queries that use DEREF in Oracle
In this lesson, we will look at an example that uses a DEREF data type to retrieve related data.
DEREF is a dereference operator that returns the value of an object. Unlike the VALUE operator, it takes a correlation argument, which is a REF (reference) to the object. Therefore, if you have a REF column within a table and you want to retrieve the target object instead of the reference to it, use DEREF. The following series of images shows the syntax for using the DEREF data type:
When the REF argument is passed to the DEREF operator, it returns the object.
DEREF returns the object instance that the REF value points to. This is more easily explained with a simple example.
In this case, DEREF is passed the REF value for the correlation ID of the inventory_tbl:
FROM inventory_tbl i
WHERE item_id = 1;
The object instance associated with the REF value of item_id 1 is returned:
In the next lesson, we will explain when PL/SQL is appropriate or required for querying object tables. Now that you have learned about the DEREF data type, click the link below to learn more about retrieving related data.
Build Deref Query
Using DEREF in a SELECT INTO Statement
emp_ref REF employee_typ;
SELECT REF(e) INTO emp_ref FROM employee_tab e
WHERE e.employee_id = 370;
The following assignment raises an error, not allowed in PL/SQL
//is not allowed, cannot use DEREF in procedural statements
SELECT DEREF(emp_ref) INTO emp FROM DUAL;
-- use dummy table DUAL
emp_name := emp.first_name || ' ' || emp.last_name;
Correlation argument: A correlation argument is a parameter, which is passed when a function is used, e.g. the VALUE finction requires the table alias to be passed as a parameter or correlation argument.