Querying Related Object Tables in Oracle 26ai

A small department relationship

The following script is illustrative. It assumes a fresh training schema with permission to create types and tables, and that the names shown are available. It has been reviewed against Oracle 26ai documentation; it has not been executed against an Oracle database as part of this lesson rewrite. Use a training environment when trying the examples.

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

CREATE TABLE departments OF department_t (
  dept_id PRIMARY KEY
);

CREATE TABLE employees (
  emp_id   NUMBER PRIMARY KEY,
  emp_name VARCHAR2(50),
  dept_ref REF department_t SCOPE IS departments
);

The first statement defines department_t. The second creates an object table whose rows have that type. The third creates an ordinary relational table containing two scalar columns and a reference column. A referencing row need not itself be a row object. References can be declared in relational-table columns as well as in object-type attributes.

The declaration REF department_t identifies the referenced object type. SCOPE IS departments restricts this column's references to the departments object table. The scope declaration does not make the column mandatory, so an employee can still have a null reference in this example. Decide separately whether an application requires every employee to have a department.

The slash on its own line submits the type definition in script-oriented clients such as SQL*Plus and SQLcl. It is a client command rather than part of the type's SQL syntax. The remaining table statements use semicolons. When using a different client or a programmatic interface, follow that client's statement submission rules instead of sending a script delimiter as though it were SQL.

Create a reference from an existing row object

Start by inserting a department value. Then select a reference to that stored row when inserting the employee:

INSERT INTO departments
VALUES (department_t(10, 'HR'));

INSERT INTO employees (emp_id, emp_name, dept_ref)
SELECT 1001, 'Alice', REF(d)
FROM departments d
WHERE d.dept_id = 10;

department_t(10, 'HR') constructs the department value used in the insertion. The alias d represents a row of the object table. The SQL function REF(d) returns a reference to that row object. It does not create another department and does not replace the department name with a numeric identifier.

The primary key makes the predicate on department identifier 10 identify at most one department. If that department exists, the insert-select adds Alice with its reference. If it does not exist, the select produces no rows and the statement inserts no employee. This is different from automatically creating an employee whose reference is null. Applications should check the outcome when a missing department would indicate an error.

The explicit destination-column list also makes the insertion easier to review. Each selected expression has a clear destination: 1001 becomes the employee identifier, Alice becomes the name, and the reference becomes dept_ref. Transaction handling remains part of the surrounding application or training session. Storing an object reference does not introduce a separate automatic commit rule.

Select attributes or retrieve the whole object

Most reports need selected attributes rather than a complete object value. This query follows the reference and projects the department name:

SELECT e.emp_id,
       e.emp_name,
       e.dept_ref.dept_name AS department_name
FROM employees e
ORDER BY e.emp_id;

Read the path from left to right. e identifies an employees row, dept_ref identifies its reference column, and dept_name identifies an attribute of the referenced department. Oracle supports implicit dereferencing for this attribute access in SQL. The table alias is significant: use an alias to qualify dot-notational references to object attributes or methods.

For the single sample employee and department, the projected values would be 1001, Alice, and HR. This is an expected result derived from the illustrative data, not a captured execution result. A client displaying a reference itself may show an encoded representation, while this projection returns a familiar scalar string.

Use explicit dereferencing when you want the referenced object value:

SELECT e.emp_id,
       DEREF(e.dept_ref) AS department_object
FROM employees e;

DEREF is a SQL function. It retrieves the object identified by its reference argument. It is not a data type to declare in a variable or a special table-creation option. The client may display the returned object in a structured format, and that format depends on the client. Choose scalar projections when a report needs plain columns.

Detect dangling references and null references

A reference can become dangling when it no longer identifies an existing target row. This can happen, for example, if the target department is removed while a stored reference remains. A null reference is a different condition: no reference value is present. Separate the two conditions when reviewing data quality.

SELECT e.emp_id, e.emp_name
FROM employees e
WHERE e.dept_ref IS DANGLING;

SELECT e.emp_id, e.emp_name
FROM employees e
WHERE e.dept_ref IS NULL;

The first query asks which employees contain dangling references. The second asks which employees have no department reference. With the illustrative insertion above and an unchanged department row, neither query would find Alice. Later lessons examine how reference validity affects retrieval and how to handle invalid associations deliberately.

A missing department name alone is not a complete validity test. The attribute itself could contain null even when the department row exists. A query that selects only the name loses information about why a value is absent. Use the reference predicates when the question concerns the relationship, and attribute predicates when it concerns the target's data.

Scope is different from referential integrity

A scope constraint identifies the object table that a reference can target. It does not by itself guarantee that the referenced row continues to exist. Oracle also supports referential integrity constraints on REF columns; their existence rules differ from scope alone. The schema designer must select the appropriate constraints instead of assuming that a reference supplies every relationship rule automatically.

For a department assignment, consider three separate questions. Is an assignment required? Which object table can provide the department? Must the referenced department exist? A nullability rule, a scope rule, and a referential integrity rule address different concerns. The training example shows scope to make navigation clear; it does not claim to implement a complete personnel application's integrity policy.

Application behavior also needs a decision when a department is closed or reorganized. Employees might be reassigned, historical assignments might be retained in a separate model, or deletion might be rejected. A query lesson cannot choose that business policy for every system. It can show how to detect the resulting states and explain why constraints and lifecycle decisions belong in the design.

When SQL is sufficient and when PL/SQL helps

Selecting object attributes, obtaining references, dereferencing objects, filtering rows, and ordering reports are SQL operations. You do not need a PL/SQL block merely because a table contains objects. Begin with a set-based SQL query when the required result is a collection of report rows.

PL/SQL is useful when database-side processing needs local variables, conditional logic, controlled iteration, exception handling, or a stored program interface. An object-table query can supply values to that processing. The following block illustrates retrieving one complete department value:

Oracle PL/SQL

SET SERVEROUTPUT ON

DECLARE
  l_department department_t;
BEGIN
  SELECT VALUE(d)
  INTO l_department
  FROM departments d
  WHERE d.dept_id = 10;

  DBMS_OUTPUT.PUT_LINE(l_department.dept_name);
EXCEPTION
  WHEN NO_DATA_FOUND THEN
    DBMS_OUTPUT.PUT_LINE('Department 10 was not found.');
END;
/

The variable is declared with the SQL object type. VALUE(d) supplies the complete row object, and the variable's dept_name attribute supplies the text for display. SET SERVEROUTPUT ON is another client command; it enables display of DBMS_OUTPUT output in clients that support it.

A non-bulk SELECT INTO expects one row. No matching row raises NO_DATA_FOUND; multiple matching rows raise TOO_MANY_ROWS. The primary key in this example prevents multiple rows with identifier 10, so the block handles the missing-row case explicitly. If you change the predicate to a nonunique attribute, review the cardinality assumptions as well as the column expression.

For several departments, use a cursor loop or another appropriate multirow retrieval technique rather than forcing the result into one object variable. Printing a message is sufficient for this demonstration, but production programs need a defined error contract. They may return a status, raise an application error, or propagate an exception to their caller.

Read the relationship before choosing the query

First identify what is stored locally and what is reached through a reference. Then decide whether the caller needs a scalar attribute, a complete object, or the reference itself. Finally, check the expected number of rows and the treatment of missing associations. These questions make the SQL expression and the procedural wrapper easier to choose.

Object references are one Oracle facility for representing navigable relationships. Ordinary foreign-key relationships remain a suitable alternative for many schemas. In this module, the goal is to understand an existing object-relational model well enough to query it accurately. Adopting object types does not require adding JSON duality, vector search, or an AI feature to a reference query.

Apply the vocabulary to a reporting request

Suppose a colleague asks for a list of employee names and their department names. Start with the employees table because each result row represents an employee. The department name is reached through dept_ref, so the scalar path expression fits the requested columns. Returning the entire department object would change the shape of the result and require the consumer to extract the name separately.

Now suppose the same colleague asks which assignments need repair. That is a different request even though it involves the same tables. A report of dangling references should test IS DANGLING. A report of employees awaiting assignment should test IS NULL. Combining both categories into one unnamed missing-department bucket would obscure which action is appropriate.

Finally, suppose a stored procedure needs the complete department before performing several decisions. Retrieving an object value into a typed variable may fit that interface. Specify how the procedure responds when the department is absent and whether the lookup can return multiple rows. The relationship determines where the data comes from; the consumer's task determines the output and processing strategy.

Oracle 26ai references

• Object types, row objects, object identifiers, and references
• REF SQL function
• DEREF SQL function
• REF constraints and object attribute name resolution
• PL/SQL SELECT INTO statement

In the next lesson, Understanding Related Object Tables, you will examine the SQL facilities used to follow these relationships.