Create a PL/SQL Block to Query Object Tables in Oracle 26ai

Create the practice objects

Use a practice schema with CREATE TYPE and CREATE TABLE privileges and sufficient tablespace quota. The names below must be unused. The prefix separates this exercise from the department objects introduced earlier in the module. Do not replace an existing application type just to run the example.

CREATE TYPE l6_employee_t AS OBJECT (
  emp_id     NUMBER,
  emp_name   VARCHAR2(100),
  emp_salary NUMBER(10,2)
);
/

CREATE TABLE l6_employee_tab OF l6_employee_t (
  emp_id PRIMARY KEY
);

INSERT INTO l6_employee_tab
VALUES (l6_employee_t(1, 'Alice', 75000));
INSERT INTO l6_employee_tab
VALUES (l6_employee_t(2, 'Bob', 65000));
INSERT INTO l6_employee_tab
VALUES (l6_employee_t(3, 'Charlie', 85000));

COMMIT;

The type defines the structure of an employee object. The object table stores one employee object per row, and the primary key makes employee identifiers unique. Each constructor supplies the three attributes in declaration order. Creating the type alone does not create employee data; the INSERT statements do that.

DDL statements such as CREATE TYPE and CREATE TABLE commit transactions. A later ROLLBACK does not remove these schema objects. The explicit COMMIT after the inserts makes the sample data persistent. Keep this setup separate from unrelated unfinished transactions, and run it once before experimenting with the retrieval blocks.

In SQL*Plus or SQLcl, the slash submits the buffered type definition. It is a client command rather than part of the type declaration. Ordinary INSERT and CREATE TABLE statements use their SQL terminators; do not indiscriminately add a slash after every statement, because a client may execute the buffered statement again.

Inspect the SQL before wrapping it in a block

SELECT e.emp_id, e.emp_name, e.emp_salary
FROM l6_employee_tab e
ORDER BY e.emp_id;

SELECT VALUE(e) AS employee_value
FROM l6_employee_tab e
ORDER BY e.emp_id;

The first query returns three scalar columns. The second returns one object-valued column for each row. VALUE accepts the alias for an object-table row and returns that row's object value. The alias employee_value names the expression so that the loop record can reference it unambiguously.

Both queries use ORDER BY. Without it, SQL does not guarantee that rows will appear in insertion order or employee-number order. The ordering is particularly useful when comparing the block's output with the expected example. Object display formatting varies between clients, so the block below prints selected attributes explicitly.

Run the complete cursor block

SET SERVEROUTPUT ON
DECLARE
  CURSOR c_employees(p_min_salary NUMBER) IS
    SELECT VALUE(e) AS employee_value
    FROM l6_employee_tab e
    WHERE e.emp_salary >= p_min_salary
    ORDER BY e.emp_id;

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

    DBMS_OUTPUT.PUT_LINE(
      'Employee ' || TO_CHAR(v_employee.emp_id, 'FM999999990') ||
      ': ' || v_employee.emp_name || ', salary ' ||
      TO_CHAR(v_employee.emp_salary, 'FM999999990D00',
              'NLS_NUMERIC_CHARACTERS=''.,''')
    );
  END LOOP;

  IF v_count = 0 THEN
    DBMS_OUTPUT.PUT_LINE('No employees meet the minimum salary.');
  ELSE
    DBMS_OUTPUT.PUT_LINE('Employees processed: ' || v_count);
  END IF;
END;
/

With the original three sample rows, the expected output is:

Employee 1: Alice, salary 75000.00
Employee 2: Bob, salary 65000.00
Employee 3: Charlie, salary 85000.00
Employees processed: 3

The salary format includes two decimal places, suppresses padding with FM, and explicitly selects a decimal point through NLS_NUMERIC_CHARACTERS. This keeps the illustrated salary display independent of the session's default decimal separator. The format is chosen for the sample values; an application should choose formats suitable for its complete value range.

SET SERVEROUTPUT ON enables DBMS_OUTPUT display in SQL*Plus or SQLcl and has no SQL semicolon here. Other clients may require an output panel or script-execution setting. DBMS_OUTPUT is useful for learning and diagnostics; it is not an application result-set interface or a durable audit log.

Understand the declaration, processing, and completion stages

The following three excerpts explain parts of the complete block. They are not separate scripts to execute. Keep the full block above as the runnable unit.

CURSOR c_employees(p_min_salary NUMBER) IS
  SELECT VALUE(e) AS employee_value
  FROM l6_employee_tab e
  WHERE e.emp_salary >= p_min_salary
  ORDER BY e.emp_id;

The declaration appears between DECLARE and BEGIN. It defines the query, but does not itself fetch rows. Notice that there is no INTO clause in the cursor definition. The cursor FOR loop handles fetching. SELECT INTO is a different PL/SQL construct for assigning a single selected row to variables.

The parameter lets the block reuse the same query with different thresholds. Passing 75000 processes Alice and Charlie; passing 90000 processes none of the initial rows. The predicate is expressed in SQL so that the query returns the desired set instead of fetching every employee and discarding rows inside the loop.

FOR rec IN c_employees(65000) LOOP
  v_employee := rec.employee_value;
  v_count := v_count + 1;
  -- Process attributes of v_employee here.
END LOOP;

The name rec is a record variable local to the loop. You do not declare it separately. Its fields correspond to the query's select list. In this example, the record has an employee_value field containing an object. Assigning that field to v_employee makes the distinction visible: rec is the query record, while v_employee is a variable of the employee object type.

Dot notation then accesses v_employee.emp_name or v_employee.emp_salary. The variable is initially null before retrieval, but the assignment supplies the selected object value before its attributes are used. Do not attempt to populate attributes of an uninitialized object variable without first constructing or retrieving an object.

END LOOP;

IF v_count = 0 THEN
  DBMS_OUTPUT.PUT_LINE('No employees meet the minimum salary.');
END IF;
END;
/

A cursor FOR loop opens its cursor, fetches successive rows, and closes it when processing finishes. It also closes the cursor if control leaves the loop or an exception occurs. This automatic lifecycle belongs to the cursor FOR-loop construct. END by itself is not a general instruction to close every manually opened cursor.

Handle zero, one, and many rows deliberately

DECLARE
  v_employee l6_employee_t;
BEGIN
  SELECT VALUE(e) INTO v_employee
  FROM l6_employee_tab e
  WHERE e.emp_id = 1;

  DBMS_OUTPUT.PUT_LINE(v_employee.emp_name);
EXCEPTION
  WHEN NO_DATA_FOUND THEN
    DBMS_OUTPUT.PUT_LINE('Employee 1 was not found.');
END;
/

Choose scalar fields when a whole object is unnecessary

BEGIN
  FOR rec IN (
    SELECT e.emp_id, e.emp_name
    FROM l6_employee_tab e
    WHERE e.emp_salary >= 75000
    ORDER BY e.emp_id
  ) LOOP
    DBMS_OUTPUT.PUT_LINE(rec.emp_id || ': ' || rec.emp_name);
  END LOOP;
END;
/

This form places the query directly in the loop. The record contains emp_id and emp_name fields, so an additional object variable is unnecessary. Use a named cursor when the name, parameters, or declaration improve clarity. Use an inline query when a short, local query is easier to read. Both cursor FOR-loop forms manage iteration automatically.

Neither form requires a REF. VALUE returns an object value; REF returns a reference to a row object; DEREF follows a reference to obtain an object value. The employee example reads directly from an object table, so VALUE is sufficient. Introducing references without a relationship to navigate would complicate the exercise.

Know what a cursor represents

Keep retrieval separate from persistent updates

UPDATE l6_employee_tab e
SET e.emp_salary = 78000
WHERE e.emp_id = 1;

Check execution and output problems

If the block does not compile, first verify that the type and table were created in the schema you are using. Check object and attribute names, statement terminators, and the END LOOP spelling. A cursor definition has no INTO clause; a single-row retrieval into variables does. The alias employee_value must match the field referenced by the loop.

If compilation succeeds but no text appears, check the client's DBMS_OUTPUT setting and script mode. Then run the underlying SELECT independently to see whether the predicate matches any data. An empty cursor result, disabled output display, and a compilation error are different conditions and need different fixes.

Test the threshold with all three sample rows, a subset, and no matches. Verify the employee order, count, and salary formatting. This checks the block's observable behavior more effectively than adding exception handlers without understanding the failure. Avoid rerunning the setup inserts blindly: the primary key will reject duplicate employee identifiers.

Native compilation is a separate tuning decision

Adapt the example without changing its meaning

When adapting this block, start with the cursor's select list and the receiving variables. If you select scalar attributes instead of VALUE, update the record-field references accordingly. If you add a calculated expression, give it a clear alias. A record field name comes from the query, not automatically from the underlying object type when the select list contains a renamed expression.

Consider null values as well. The sample inserts non-null salaries, but the table definition does not prohibit a null salary. Such a row does not satisfy the minimum-salary comparison, so it will be excluded. If your business rule treats missing salaries differently, express that rule explicitly in the query and describe it in the output. Do not silently treat an unknown salary as zero.

Review the block's responsibilities

1. What does VALUE(e) return? The selected row as an employee object value.
2. Who declares rec? The cursor FOR loop, using the query's result fields.
3. What happens with zero matches? No iterations occur; the explicit counter detects that outcome.
4. Does assigning an attribute persist a change? No. A local object variable and a stored row are distinct.
5. Who closes the cursor? The cursor FOR loop manages its cursor automatically.

Continue to the object-relational module conclusion to review how object tables, references, SQL queries, and PL/SQL blocks fit together.

Oracle 26ai references

• Cursor FOR LOOP statement
• Object manipulation in PL/SQL
• Compiling PL/SQL units for native execution