Query Related Object Tables with REF, DEREF and VALUE

Oracle AI Database 26ai supports relationships between row objects through typed REF values. A sale can store a reference to a customer, for example, and a query can follow that reference to retrieve the customer's name. The relationship is represented by a reference to a stored object rather than by an embedded copy of every customer attribute.

This lesson explains three SQL functions: REF, which obtains a reference to a row object; DEREF, which retrieves the referenced object; and VALUE, which retrieves a row object directly from its object-table alias. You will also compare these results with scalar attribute projections and an explicit reference join. The examples use one consistent customer and sale model so that each query's input and output are clear.

Identify the object that can be referenced

An object type defines attributes and optional methods. An object table stores instances of that type as row objects. An object stored inside a relational-table column is instead a column object. Although both contain typed attribute values, an embedded column object does not automatically become an independently referenceable row object.

The SQL REF function accepts an alias associated with a row of an object table or an object view. It does not turn an arbitrary relational row into an object reference. A conventional foreign-key column is therefore not enough to make REF(alias) applicable to its target table. Determine the table's actual definition before choosing the expression.

Oracle gives row objects object identifiers, or OIDs. System-generated identifiers are the default; primary-key-based object identifiers are another table-definition option. A scalar primary key and an object identifier have different roles unless the table explicitly uses the primary-key-based option. In the training model below, the customer identifier is a business key while row-object identity uses Oracle's default mechanism.

Populate the relationship with REF(c)

INSERT INTO customers
VALUES (customer_type(101, 'Maya', 'Chen', DATE '2026-10-12'));

INSERT INTO sale_header (sale_id, cust_ref, sale_date, sale_total)
SELECT 5001, REF(c), DATE '2026-10-12', 42.50
FROM customers c
WHERE c.cust_id = 101;

The constructor expression creates a customer value for insertion into the object table. In the second statement, the alias c identifies the stored customer row. REF(c) obtains its reference, and the insert-select stores that reference in the sale. Declaring cust_ref alone would not establish any relationship.

The predicate on the primary key identifies at most one customer. If customer 101 exists, the statement inserts one sale. If the customer is absent, the select returns no rows and the statement inserts no sale. That result is different from inserting a sale with a null customer reference. An application should check whether this missing-target condition is acceptable.

The column list makes the correspondence explicit: each selected expression maps to one destination column. The amount is a numeric literal; the dates are typed date literals. The script does not include a commit, because transaction completion depends on the session or application running the examples. Object-relational DML still requires ordinary transaction decisions.

Choose the result your query needs

A report might need a customer's surname, a complete customer object, or a reference that another operation will store. Those outputs are different data values. Select the appropriate expression before considering the shape of the surrounding query.

The term correlation variable^[1] in the REF and VALUE documentation means the table alias in these examples. It represents a row supplied by the FROM clause. It is not a numeric customer identifier and is not an arbitrary string containing a table name.

REF returns identity, not customer attributes

SELECT c.cust_id, REF(c) AS customer_reference
FROM customers c
WHERE c.cust_id = 101;

The query returns the business identifier alongside a reference value. A SQL client might display the reference as encoded text or another driver-specific representation. That display is not a portable business identifier or a documented format that the application should parse. The customer identifier remains the appropriate readable value in this example's reports.

A reference's internal representation depends on its definition and storage context. Do not assume every REF has the same byte layout or always contains a ROWID component. References can assist object navigation, but their internal encoding is not the lesson's application contract.

REF is also not inherently more reliable than a foreign key. Reliability depends on the declared integrity rules and how the application changes or removes data. A scoped reference can still become dangling. Likewise, navigation syntax does not guarantee that a query is faster than an explicit join. Evaluate an actual workload, indexes, cardinality and execution plan before making a performance claim.

VALUE returns the row object

SELECT VALUE(c) AS customer_object
FROM customers c
WHERE c.cust_id = 101;

VALUE(c) returns a customer object, rather than a reference to it. A client that understands Oracle object types can inspect its attributes. A text-oriented client might show a constructor-like representation, but that presentation is client dependent. This lesson does not prescribe an exact display string as proof of the object's contents.

For a conventional tabular report, select the attributes explicitly:

SELECT c.cust_id, c.first_name, c.last_name
FROM customers c
WHERE c.cust_id = 101;

The logical scalar result for the illustrative row would be 101, Maya, and Chen. That expected result follows from the inserted values; it is not a captured execution log. Explicit attribute projection gives a report a predictable set of columns without requiring its consumer to decode a structured object value.

DEREF follows an existing reference

SELECT s.sale_id, DEREF(s.cust_ref) AS customer_object
FROM sale_header s
WHERE s.sale_id = 5001;

The sale alias provides the reference column. DEREF uses that reference to retrieve its target object. The argument is therefore s.cust_ref, not s and not a scalar customer identifier. A reference function and a dereference function move in different directions: one obtains identity from a row object, while the other retrieves the object from its identity.

For selected attributes, Oracle supports implicit dereferencing through a qualified path:

SELECT s.sale_id,
       s.cust_ref.first_name AS customer_first_name,
       s.cust_ref.last_name AS customer_last_name,
       s.sale_total
FROM sale_header s
ORDER BY s.sale_id;

Use the table alias to qualify dot-notational references to object attributes. The path identifies the sale, its reference, and the target attribute. The query is a plain SQL statement; it does not require a PL/SQL block simply because it navigates an object relationship.

Use typed date predicates in reference queries

The legacy examples formatted a date as a language-dependent string before comparing it. A half-open range describes a calendar day directly and includes values with nonmidnight times:

SELECT c.cust_id, REF(c) AS customer_reference
FROM customers c
WHERE c.last_update_date >= DATE '2026-10-12'
  AND c.last_update_date < DATE '2026-10-13';

Oracle DATE includes a time component. Equality to a date literal would identify midnight rather than every moment in that day. The range begins at the requested day's midnight and ends just before the next day's midnight. It avoids converting each stored date to a month-name string and leaves the stored column unwrapped for the optimizer to consider available access paths.

This does not promise an index scan. Plans depend on the available indexes and the data. For TIMESTAMP columns, use matching timestamp expressions; for time-zone-aware requirements, define which zone determines the reporting day's boundaries. The example concerns a plain DATE column in the simplified customer type.

REF CURSOR is a different mechanism

A PL/SQL cursor variable identifies a query's result set. SYS_REFCURSOR is a predefined weak REF CURSOR type, often used by stored programs to return query results to callers. It is not a REF to a customer row object and does not replace the persistent customer association in the sale table.

A cursor variable has an operational lifecycle: a query is opened, rows are fetched, and the cursor is closed. An object REF instead represents a typed association with a row object. Sharing the word REF does not make their storage rules, arguments or operations interchangeable. Do not pass a cursor variable to DEREF to obtain a customer.

A stored program can open a cursor for a query that uses REF-related expressions, just as it can open a cursor for a conventional relational query. That combines two facilities with separate roles: SQL determines the data, while the cursor interface delivers a result set. Collection element restrictions and cursor portability are separate topics and are not prerequisites for writing the reference queries in this lesson.

[1] Correlation variable: In these object-table SQL functions, the correlation variable is the table alias from the FROM clause, such as c in FROM customers c. REF(c) and VALUE(c) use that alias to identify the current row object.