Querying Oracle Object Tables: Module Conclusion

What you should now be able to do

1. Keep types, objects, and references distinct

The introductory lesson established the vocabulary. An object type is a schema definition containing named attributes and optional methods. An object value supplies values for that structure. An object table stores objects as rows, while a relational table can contain an object-valued column or a REF column alongside ordinary scalar columns.

A REF is a typed logical reference to a row object. It is not a copy of the object's attributes or an application memory address. Several source rows can reference the same target object, which is useful when the association represents shared data. An embedded column object, by contrast, is not automatically an independently referenceable row object.

Object identity and business identifiers also need separate treatment. A department number can be a primary key, while Oracle uses a system-generated object identifier for the row object. Declaring the primary key alone does not select primary-key-based object identifiers. Deleting a row and inserting another with the same business identifier does not automatically reconnect old system-generated references.

This distinction helps when interpreting query output. A hexadecimal REF display in a client is a representation of the reference, not the department's business identifier. A constructor-like display of an object is also client-dependent. Select named attributes when a report needs ordinary values instead of relying on the display format of a composite result.

2. Match REF, VALUE, and DEREF to their inputs

Querying related object tables developed the operations that create and follow relationships. Remember their arguments as well as their results:

In the Lesson 4 schema, an employee row holds a reference to a department object. The employee table itself is relational. This demonstrates that the referencing row does not need to be an object-table row merely because it stores a REF.

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

This returns the employee name and one object-valued column. If the consumer needs only the department name and location, project those fields instead:

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.emp_id = 101;

The table alias makes the path explicit. SQL follows the department reference to retrieve the named attributes. DEREF is a function or operator, not a data type. Use REF followed by the actual object type when declaring a reference variable, and use the object type itself when declaring a variable that will hold the returned object value.

3. Treat missing references as a design question

The dangling-reference lesson distinguished two situations. A null REF means that no reference value is stored. A dangling REF contains a reference whose target is unavailable, for example because the target row was deleted. The source row can still exist in either situation.

SQL DEREF of a dangling REF returns a null object. It does not inevitably raise an exception. A query that only displays the dereferenced object may therefore obscure the difference between a missing reference and a missing target. Use the REF predicates when that distinction matters.

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
ORDER BY e.emp_id;

This is appropriate for examining incomplete associations because it keeps the employee rows visible. A report restricted to employees with available departments can instead apply both IS NOT NULL and IS NOT DANGLING. That filter changes the report population; it does not repair the relationship.

SCOPE restricts which object table a REF can target. It does not alone guarantee that the referenced row continues to exist. Referential constraints and deletion policies address a different requirement. Decide whether deletion should be prevented, references should be changed, or affected source rows should be removed through an explicit business process.

Do not confuse these object-reference rules with reference partitioning or interval partitioning. Partitioning organizes stored data for other purposes and is outside this module's retrieval workflow. It is not a replacement for designing the integrity of REF relationships.

4. Use SQL inside PL/SQL to retrieve a referenced object

Writing DEREF queries connected SQL retrieval to PL/SQL variables. Procedural PL/SQL does not navigate REF chains through ordinary assignments. The correct pattern is to execute SQL that dereferences the value and assign the result through SELECT INTO:

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);
  END IF;
EXCEPTION
  WHEN NO_DATA_FOUND THEN
    DBMS_OUTPUT.PUT_LINE('Employee not found.');
END;
/

The variable is an object value, so its attributes can be accessed after retrieval. The null check handles an existing employee whose reference does not produce an available object. NO_DATA_FOUND handles an absent employee. These are separate conditions even when both mean that the desired department cannot be displayed.

The primary key limits this query to at most one employee. If you replace the predicate with a nonunique condition, SELECT INTO can raise TOO_MANY_ROWS. Choose a multirow mechanism when several rows are legitimate rather than treating the extra rows as an unexpected nuisance.

When a REF is already stored in a PL/SQL variable, the SQL expression can use DUAL: SELECT DEREF(v_ref) INTO v_dept FROM DUAL. A direct assignment such as v_dept := DEREF(v_ref) is not the procedural equivalent. This difference concerns where the expression executes, not whether the reference is valid.

5. Choose SQL or procedural processing deliberately

When to use PL/SQL corrected the assumption that objects and collections inherently require procedural code. SQL can filter rows, join related data, aggregate values, query supported collections, and invoke eligible functions. A simple update by a key can also be a single SQL statement.

PL/SQL is useful when a database-side operation needs to sequence those statements with local state, procedural calls, branching, or exception handling. An application language can also coordinate operations. The location of that logic should follow the application's requirements rather than the existence of an object type alone.

Object methods require the same care. Eligible member or static functions can participate in SQL expressions, subject to the applicable invocation restrictions. A procedure is not a scalar SELECT expression. Changing a fetched object's attributes through a procedure changes the local value unless the code also performs an appropriate persistent write.

UTL_REF supplies procedures for reference-based operations. SELECT_OBJECT retrieves a value; LOCK_OBJECT performs locking and has an overload that also retrieves; UPDATE_OBJECT and DELETE_OBJECT perform their respective changes. Package exception behavior is not necessarily identical to SQL DEREF returning a null object. Consult the operation's contract rather than assigning one missing-object behavior to every interface.

Performance claims also need context. BULK COLLECT and FORALL can reduce repeated communication between PL/SQL and SQL compared with row-by-row processing, but they do not make a procedural solution automatically better than a set-based statement. Fetch only what the workflow needs, consider memory use, and measure actual execution when performance matters.

6. Let a cursor FOR loop manage a result set

Creating a PL/SQL block demonstrated retrieval from a separate employee object table. With the Lesson 6 setup in place, the following review block processes employee objects in identifier order:

DECLARE
  CURSOR c_employees IS
    SELECT VALUE(e) AS employee_value
    FROM l6_employee_tab e
    ORDER BY e.emp_id;

  v_employee l6_employee_t;
  v_count PLS_INTEGER := 0;
BEGIN
  FOR rec IN c_employees LOOP
    v_employee := rec.employee_value;
    v_count := v_count + 1;
    DBMS_OUTPUT.PUT_LINE(v_employee.emp_name);
  END LOOP;

  DBMS_OUTPUT.PUT_LINE('Employees processed: ' || v_count);
END;
/

The cursor declaration contains a SELECT without INTO. The loop automatically declares its record, opens the cursor, fetches successive rows, and closes it on completion. It also closes its cursor if control leaves the loop or an exception occurs. END alone is not a general instruction to close manually managed cursors.

The loop record and the object variable have different roles. The record's employee_value field contains the object returned by VALUE. Assigning it to v_employee lets the block access the object's attributes. If the query selected scalar fields instead, the record would contain those fields and a separate object variable might be unnecessary.

With no employee rows, the loop runs zero times and the counter remains zero. That condition does not raise NO_DATA_FOUND. Use SELECT INTO for an expected single row, and use cursor processing when zero, one, or many rows are valid outcomes. Explicit ordering is needed when a repeatable display order is required.

SET SERVEROUTPUT ON and the slash delimiter are SQL*Plus or SQLcl client conventions. Other tools may require script mode or a DBMS_OUTPUT panel. Output messages help explain a demonstration, but they neither commit changes nor serve as a durable application audit trail.

Review a relationship from question to result

Suppose a department directory must list employees and the names of their assigned departments. First inspect the schema: is the association a REF, an ordinary foreign key, or an embedded object? That determines which query expressions are appropriate. A column's name alone does not establish its declared type.

Next define the report population. Should employees without a department be retained? Should a dangling reference be shown as a data-quality problem? A path predicate, an inner join, and an unfiltered DEREF projection can retain different rows. Write the missing-data rule before treating alternative query forms as equivalent.

Then choose the result shape. A display usually needs scalar attributes; an object-oriented interface may need a complete typed object. If the consumer only reads the results, a SQL query may finish the task. Add a PL/SQL block when the workflow requires procedural processing around that query.

Finally, test cardinality and state. Check an existing source row, an absent source row, a null REF, and a dangling REF where the test schema permits one. For cursor processing, check an empty set and several rows. Use a training environment and explicit transaction decisions when changing sample data to create these cases.

Keep persistent changes and local values separate

Glossary

Check your understanding before the quiz

1. Which operation returns a row object directly from its table alias? VALUE. REF returns a reference, while DEREF follows a REF-valued expression.
2. Does SCOPE alone prevent a deleted target from leaving a dangling reference? No. Target-table scope and referential integrity are different constraints.
3. Why might a missing employee and a missing department require separate handling? The former can produce no source row; the latter can produce a null dereferenced object for an existing employee.
4. Does a collection or object method always require PL/SQL? No. SQL supports collection queries and eligible functions.
5. What does an empty cursor FOR loop do? It executes no iterations. The program can detect that outcome with an explicit count or other suitable logic.
6. Does changing an object variable change the stored row? No. Persistence requires a database write.

Querying Object Tables Quiz

Oracle 26ai references

• Object-relational features, references, and dereferencing
• Object manipulation in PL/SQL
• Cursor FOR LOOP statement