Oracle 26ai Object Tables: When to Use SQL or PL/SQL

Choose the operation before choosing the language

Use SQL for attributes, objects, and references

Query an object table's attributes much as you would query columns in a relational table. The following statement returns scalar values for matching department rows:

SELECT d.dept_id, d.dept_name
FROM l4_dept_tab d
WHERE d.location = 'Austin';

If the caller needs each matching row as an object value, use VALUE with the object-table alias:

SELECT VALUE(d) AS department_object
FROM l4_dept_tab d
WHERE d.dept_id = 10;

VALUE returns the department object rather than three separate result columns. A client might display it using constructor-like notation or an expandable object view. That presentation is client-dependent. The underlying result remains a value of the department object type.

For a reference held in an employee row, SQL can either follow a path to an attribute or return the whole referenced object:

SELECT e.emp_name, e.dept_ref.dept_name AS department
FROM l4_emp_tab e
WHERE e.dept_ref IS NOT NULL
  AND e.dept_ref IS NOT DANGLING;

SELECT e.emp_name, DEREF(e.dept_ref) AS department_object
FROM l4_emp_tab e;

The first query implicitly dereferences the department REF and selects its name. Its predicates intentionally exclude employees whose references are null or dangling. The second query retains employee rows and projects a complete department object where available. These queries have different result shapes and different missing-data behavior; they are not interchangeable merely because both access departments.

A REF is distinct from an object value and from an ordinary numeric identifier. A PL/SQL variable receiving a department reference would be declared REF l4_dept_t; a variable receiving VALUE or DEREF would be declared l4_dept_t. Matching these types prevents the error of assigning a reference to an ordinary object variable.

Use one SQL statement when it performs the change

Changing a known department's location does not require fetching its reference first:

UPDATE l4_dept_tab d
SET d.location = 'Chicago'
WHERE d.dept_id = 10;

This UPDATE changes the stored row in the current transaction. The application or person running the transaction decides whether to commit or roll back. No PL/SQL loop is needed to locate the row, and an extra REF lookup would add work without changing this example's requirement.

When applying a similar change to many rows, first ask whether an appropriate WHERE clause can select the entire set. Fetching every row into PL/SQL and issuing a separate UPDATE for each one can introduce avoidable communication between the PL/SQL and SQL engines. A single statement also expresses the affected set in one place.

If a trigger is defined for an affected table event, Oracle invokes it according to its definition. The caller does not need to wrap a SQL statement in PL/SQL merely because triggers exist. Keep trigger behavior, caller language, and transaction ownership as separate design questions.

Use PL/SQL to organize procedural work

The next block retrieves a department object and chooses a message based on its location. It illustrates variables, embedded SQL, branching, and a defined response when the department is absent:

SET SERVEROUTPUT ON
DECLARE
  v_dept l4_dept_t;
BEGIN
  SELECT VALUE(d) INTO v_dept
  FROM l4_dept_tab d
  WHERE d.dept_id = 10;

  IF v_dept.location = 'Austin' THEN
    DBMS_OUTPUT.PUT_LINE('Route to the Austin review team.');
  ELSE
    DBMS_OUTPUT.PUT_LINE('Route to the general review team.');
  END IF;
EXCEPTION
  WHEN NO_DATA_FOUND THEN
    DBMS_OUTPUT.PUT_LINE('Department 10 was not found.');
END;
/

VALUE supplies the object value for v_dept. Subsequent dot notation accesses attributes of that local object variable. The branch could be expressed as a SQL CASE expression if producing a message were the entire requirement. PL/SQL becomes more useful when each branch must coordinate additional procedural steps.

The sample's output depends on the current data. With the original Lesson 4 setup it selects the Austin message. If you executed the preceding location UPDATE and have not rolled it back, it selects the general message. Examples that modify data affect later queries in the same session.

The primary-key predicate selects at most one department. No matching row raises NO_DATA_FOUND in SELECT INTO. A different, nonunique predicate could return multiple rows and raise TOO_MANY_ROWS. For a multirow requirement, choose a cursor or another multirow retrieval mechanism instead of hiding a cardinality error with a broad exception handler.

Resolve a REF inside a PL/SQL block

PL/SQL procedural assignments cannot directly follow a REF chain in the way a SQL path expression can. You can use embedded SQL to retrieve the referenced object and then work with its attributes:

DECLARE
  v_ref  REF l4_dept_t;
  v_dept l4_dept_t;
BEGIN
  SELECT e.dept_ref INTO v_ref
  FROM l4_emp_tab e
  WHERE e.emp_id = 101;

  SELECT DEREF(v_ref) INTO v_dept FROM DUAL;

  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 101 was not found.');
END;
/

The second SELECT demonstrates that a supplied REF can be dereferenced without naming its target object table in that statement. DUAL provides a row for evaluating the expression. The first SELECT still needs the employee table because that is where this example obtains the reference.

An absent employee and an unavailable department are different outcomes. The first raises NO_DATA_FOUND; the second can yield a null object from DEREF. SQL dereferencing of a dangling REF returns a null object, rather than invariably raising an exception. Handle the outcome that belongs to the operation being used.

Understand what UTL_REF adds

Oracle's UTL_REF package provides procedural operations on objects identified by references. It is useful for generic code that receives a REF and should not hard-code the target table name. Its procedures remain database operations; using the package is not a guarantee of client-side caching or zero database I/O.

• UTL_REF.SELECT_OBJECT retrieves an object's value into a compatible PL/SQL variable.
• UTL_REF.LOCK_OBJECT locks the referenced object; an overload also retrieves it.
• UTL_REF.UPDATE_OBJECT writes a supplied object value to the referenced object.
• UTL_REF.DELETE_OBJECT deletes the referenced object.

SELECT_OBJECT is not the locking operation. If a workflow needs a protected read-modify-write sequence, design the locking or concurrency check explicitly. Transaction completion releases transaction locks. Do not leave a transaction open while waiting indefinitely for user input.

Consult the package's documented exception behavior before substituting a UTL_REF procedure for a SQL DEREF expression. A missing object, a null REF, insufficient privileges, or a concurrency conflict can produce package-operation errors. Do not assume that every retrieval mechanism handles unavailable objects identically.

Distinguish object methods from ordinary queries

Object types can declare member functions, member procedures, and static methods. Eligible functions can be invoked from SQL expressions. Therefore, the existence of object methods does not automatically require PL/SQL. A member procedure, however, is not a scalar expression that returns a value to a SELECT list.

SQL-invoked functions must satisfy the applicable SQL invocation and side-effect restrictions. Declaring a function DETERMINISTIC is not a general requirement for calling it from SQL, and it does not grant permission to modify data in a prohibited context. Similarly, calling a PL/SQL function from SQL does not guarantee parallel execution or eliminate all engine-transition overhead.

A procedure that changes attributes of a fetched object variable changes that local value. Persisting the result requires an appropriate database write. Fetching, modifying, and writing an object can also overwrite intervening changes unless the routine uses a suitable concurrency strategy. The Lesson 4 department type has no methods, so these examples do not call invented methods on it.

Subtype-aware data does not automatically require procedural code either. SQL offers type inspection and conversion facilities, while PL/SQL may be convenient when different subtypes lead to different sequences of operations. Choose based on the workflow rather than the presence of an inheritance hierarchy alone.

Query collections with SQL before adding loops

A SQL collection value can be exposed as rows with TABLE. For a small independent demonstration, create the following type in a practice schema where its name is unused:

CREATE TYPE l5_name_list_t AS TABLE OF VARCHAR2(50);
/

SELECT n.COLUMN_VALUE AS department_name
FROM TABLE(l5_name_list_t('Cloud Architecture', 'Research')) n
ORDER BY n.COLUMN_VALUE;

The constructor creates a nested-table value containing two strings. TABLE exposes its elements as rows, and COLUMN_VALUE names the scalar element column. The ORDER BY clause determines the display order. COLUMN_VALUE is appropriate for this scalar collection; it is not a universal substitute for VALUE on an object-table alias.

CREATE TYPE is DDL and commits; a later ROLLBACK does not remove the type. Keep this setup separate from unfinished transaction experiments. SQL can also query supported stored nested-table and varray collections. Use procedural iteration when the processing requirement needs it, not simply because the data is a collection.

Use bulk processing for an established procedural need

When processing must occur in PL/SQL, BULK COLLECT retrieves rows in batches and FORALL batches DML bindings. These features can reduce repeated communication between PL/SQL and SQL compared with issuing individual statements for each row. They do not establish that procedural processing is faster than one suitable SQL statement.

An unrestricted BULK COLLECT can load a large result into a collection. For large workloads, consider bounded fetching with FETCH BULK COLLECT LIMIT, process each nonempty batch, and stop after a fetch returns no elements. Choose and measure a batch size appropriate to the data and session memory budget.

FORALL is a DML batching construct, not a general loop for arbitrary method calls or procedural branches. Collection bounds, sparse indexes, and error-handling requirements need deliberate treatment. Do not copy a bulk example until its declarations, element types, target columns, and empty-collection behavior fit the actual operation.

Retrieving an object once can avoid repeating the same retrieval during a routine, but the variable holds a fetched value. It is not a continuously synchronized view of the stored object. Avoid claims that materializing objects always uses a particular memory region or removes all future database access.

Recognize the structure of an anonymous block

DECLARE
  v_name VARCHAR2(50);
BEGIN
  SELECT d.dept_name INTO v_name
  FROM l4_dept_tab d
  WHERE d.dept_id = 10;

  DBMS_OUTPUT.PUT_LINE(v_name);
EXCEPTION
  WHEN NO_DATA_FOUND THEN
    DBMS_OUTPUT.PUT_LINE('No matching department.');
END;
/

Apply the choice to your requirement

Begin with the result's shape and cardinality: one object, several rows, a scalar calculation, or a data change. Identify the SQL that produces it. Add PL/SQL when a database-side routine needs to coordinate that work with procedural state, calls, or recovery decisions. Keep transaction ownership explicit, especially for routines called by larger business transactions.

1. Does a REF column require PL/SQL? No. SQL supports REF navigation and DEREF.
2. Does a nested table require a loop? No. SQL TABLE expressions can expose collection elements.
3. Does SELECT_OBJECT lock the target? No. UTL_REF provides LOCK_OBJECT for locking.
4. Is bulk PL/SQL always faster? No. Compare it with a set-based solution and measure the actual workload.

Continue to creating a PL/SQL block to develop the procedural structure further.

Related reading: Oracle PL/SQL Programming.

Oracle 26ai references

• Object manipulation and REF restrictions in PL/SQL
• Operations on collection data types
• UTL_REF package operations and exceptions
• PL/SQL functions that SQL statements can invoke
• Bulk SQL and bulk binding