Writing DEREF Queries in Oracle AI Database 26ai

Distinguish REF, VALUE, and DEREF

Oracle provides three related operations with different inputs. REF(d) creates a reference to the row object represented by table alias d. VALUE(d) returns that row as an object value. DEREF(e.dept_ref) follows a reference stored in another row and returns the referenced object. The alias supplied to REF or VALUE is a correlation variable^[1]; DEREF instead takes a REF-valued expression.

A department number is not automatically a REF. An ordinary numeric foreign key and an object reference represent relationships differently. If your existing schema uses numeric department keys, use an appropriate relational join. DEREF applies when the schema actually stores Oracle object references.

Prepare a small object-relational example

CREATE TYPE l4_dept_t AS OBJECT (
  dept_id   NUMBER,
  dept_name VARCHAR2(50),
  location  VARCHAR2(50)
);
/

CREATE TABLE l4_dept_tab OF l4_dept_t (
  dept_id PRIMARY KEY
);

CREATE TABLE l4_emp_tab (
  emp_id   NUMBER PRIMARY KEY,
  emp_name VARCHAR2(50),
  dept_ref REF l4_dept_t SCOPE IS l4_dept_tab
);

INSERT INTO l4_dept_tab
VALUES (l4_dept_t(10, 'Cloud Architecture', 'Austin'));

INSERT INTO l4_emp_tab (emp_id, emp_name, dept_ref)
SELECT 101, 'Alice Rivera', REF(d)
FROM l4_dept_tab d
WHERE d.dept_id = 10;

COMMIT;

Retrieve the complete referenced object

SELECT e.emp_name,
       DEREF(e.dept_ref) AS department_object
FROM l4_emp_tab e
WHERE e.emp_id = 101;

The FROM clause identifies the table that holds the reference. It need not be the object table that holds the referenced department. The WHERE clause selects employee 101, while DEREF retrieves that employee's department value. The expression does not expand the object into three independent columns.

For the setup above, the result contains Alice Rivera and a department object whose values are 10, Cloud Architecture, and Austin. A client may display that object using constructor-like notation such as L4_DEPT_T(10, 'Cloud Architecture', 'Austin'). Other clients may present an expandable object. Treat this as an illustrative representation, not a fixed output format.

If you select e.dept_ref directly, the result is the reference. Some SQL clients show it as a hexadecimal representation. Do not build application logic around that display or mistake it for the department number. Use the object attributes when the result must expose business information.

Retrieve individual attributes with SQL path expressions

SELECT e.emp_name,
       e.dept_ref.dept_name AS department,
       e.dept_ref.location AS office_location
FROM l4_emp_tab e
WHERE e.dept_ref.dept_id = 10;

Read e.dept_ref.dept_name from left to right: start with the employee row identified by alias e, follow its department reference, and read the department object's name attribute. Oracle supports this implicit dereferencing in SQL. Use a table alias consistently so that the parser can resolve object paths correctly.

The query returns separate employee, department, and office columns. For the initial data, their values are Alice Rivera, Cloud Architecture, and Austin. The predicate follows the same reference to test the department identifier. Multiple employees can reference department 10, so this form can return several rows once more employees are inserted.

Choose path expressions when a report needs a few fields. Choose DEREF when the consumer needs a typed object value, for example to place it in an object variable. This distinction concerns the shape of the result. It does not establish that one form is always faster; actual performance depends on the query, data, access paths, and execution plan.

Compare direct object access and reference navigation

SELECT REF(d) AS department_ref,
       VALUE(d) AS department_value,
       DEREF(REF(d)) AS dereferenced_value
FROM l4_dept_tab d
WHERE d.dept_id = 10;

SELECT e.emp_name, d.dept_name, d.location
FROM l4_emp_tab e
JOIN l4_dept_tab d ON e.dept_ref = REF(d)
WHERE e.emp_id = 101;

Use SELECT INTO to retrieve an object in PL/SQL

SET SERVEROUTPUT ON
DECLARE
  v_dept l4_dept_t;
BEGIN
  SELECT DEREF(e.dept_ref)
  INTO v_dept
  FROM l4_emp_tab e
  WHERE e.emp_id = 101;

  IF v_dept IS NULL THEN
    DBMS_OUTPUT.PUT_LINE('Department unavailable.');
  ELSE
    DBMS_OUTPUT.PUT_LINE(
      v_dept.dept_name || ' (' || v_dept.location || ')'
    );
  END IF;
EXCEPTION
  WHEN NO_DATA_FOUND THEN
    DBMS_OUTPUT.PUT_LINE('Employee not found.');
END;
/

Decide how to handle missing references

A null REF indicates that no reference value is stored. A dangling REF contains a reference whose target object is unavailable, for example after deletion. As Oracle's object-relational documentation explains, SQL dereferencing of a dangling REF returns a null object. It does not inevitably raise the error claimed by the older version of this lesson.

When a report should include only employees with usable department references, make that requirement explicit:

SELECT e.emp_name,
       DEREF(e.dept_ref) AS department_object
FROM l4_emp_tab e
WHERE e.dept_ref IS NOT NULL
  AND e.dept_ref IS NOT DANGLING;

The two predicates communicate separate requirements: a reference must be present, and its target must be available. This query omits employees who fail either requirement. That may be correct for a department directory, but unsuitable for a data-quality report whose purpose is to reveal incomplete relationships.

To investigate the stored references without excluding affected employees, classify them:

SELECT e.emp_id, e.emp_name,
       CASE
         WHEN e.dept_ref IS NULL THEN 'No reference'
         WHEN e.dept_ref IS DANGLING THEN 'Dangling reference'
         ELSE 'Available reference'
       END AS department_status
FROM l4_emp_tab e;

Filtering or classifying references does not repair them. It also does not create a referential integrity rule. Revisit the previous lesson when deciding how deletions should be handled. A scoped REF and a REF protected by a referential constraint have different guarantees, even when both point into the same object table.

Check the query before changing the schema

If a query fails or returns unexpected results, first inspect the declared column types and the rows selected by its predicate. Verify that the DEREF argument is actually a REF and that it targets the intended object type. Check table aliases and attribute spelling. Do not assume that a column named product_id or dept_id is a reference merely because its name suggests an identifier.

Next, separate result-shape issues from missing-data issues. An object-valued column is expected from DEREF; scalar columns are expected from attribute paths. Zero selected employee rows, a null reference, and a dangling reference are distinct situations. In PL/SQL, check both SELECT INTO cardinality and whether the resulting object is null.

Finally, verify client execution mode. A type definition and an anonymous block require suitable statement delimiters when run as a script. Use straight single quotes for text literals, and do not copy HTML line-break tags into SQL. The examples here are accessible text so you can inspect each statement before running it.

Read the sample results in context

Before extending the example, run the employee query and the department query separately. Confirm that employee 101 exists and that department 10 has the expected name and location. This establishes the starting data for interpreting the reference queries. If you rerun the inserts without resetting the practice schema, primary-key violations are expected because the identifiers already exist; they do not indicate a problem with DEREF.

Suppose another employee later references the same department object. Both employee rows can retrieve that department through their respective stored references. If the department location is changed and committed, subsequent queries can retrieve the updated location without rewriting a location value in each employee row. A previously fetched PL/SQL object variable, however, remains the value that was retrieved until your code fetches or changes it again.

Keep object identity separate from matching attribute values. Deleting a department row and inserting a new row with the same department number does not automatically reconnect existing references when system-generated object identifiers are used. The new row has a new identity. This is one reason to design deletion and integrity rules explicitly instead of treating a REF as an encoded business key.

Check your understanding

1. Which expression returns a department object from an employee's reference? Use DEREF(e.dept_ref). Selecting the REF alone returns the reference value.
2. How do you return just the department name in SQL? Use an alias-qualified path such as e.dept_ref.dept_name.
3. Does SCOPE prevent dangling references? No. It restricts the target table; referential integrity requires a separate constraint design.
4. How do you obtain that object in PL/SQL? Use SELECT DEREF(...) INTO an object variable, then access the variable's attributes.

You can now choose between returning a reference, returning an object, and projecting individual attributes. Continue to querying object tables to examine when SQL alone is sufficient and when PL/SQL is appropriate.

[1] Correlation variable: A table alias associated with a row in a query. REF and VALUE accept an object-table row alias; DEREF accepts an expression whose value is a REF.

Oracle 26ai references

• SQL Language Reference: DEREF
• Object-relational features: REFs, dereferencing, and object identifiers
• Object manipulation and REF restrictions in PL/SQL