Lesson 4	Writing queries that use DEREF
Objective	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^[1], 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 SlideShow shows the syntax for using the DEREF data type:

Oracle Deref Syntax

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

DECLARE
emp employee_typ;
emp_ref REF employee_typ;
emp_name VARCHAR2(50);
BEGIN
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

emp_name := emp_ref.first_name || ' ' || emp_ref.last_name;
emp := DEREF(emp_ref);

//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;
DBMS_OUTPUT.PUT_LINE(emp_name);
END;
/

[1]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.

PL/SQL Programming