PL/SQL Procedure for Nested Table Queries (Cursor and FOR LOOP Pattern)

Why PL/SQL for Nested Table Retrieval?

The SALE_HEADER Schema — Parent-Child Relationship

Why Two FOR LOOPs Are Required

A single loop structure cannot traverse two dimensions of data simultaneously. The cursor loop operates at the dimension of SALE_HEADER rows — one iteration per sale. Within each sale, detail_nest is a collection containing zero or more SALE_DETAIL elements. To access those elements individually, a second loop is required — one that iterates from the first element (index 1) to the last element (index nested_data.COUNT). The outer loop controls which sale is being processed; the inner loop controls which detail element within that sale is being processed.

The Complete PL/SQL Block — Figure m4-7.1

DECLARE
    sale_id       sale_header.sale_id%TYPE;
    customer_name sale_header.customer_name%TYPE;
    nested_data   sale_header.detail_nest%TYPE;
    CURSOR c_sales IS
        SELECT sale_id, customer_name, detail_nest
        FROM   sale_header
        WHERE  sale_id IN (1, 2, 3);
BEGIN
    -- Open the cursor and begin looping through the result set
    OPEN c_sales;
    LOOP
        FETCH c_sales INTO sale_id, customer_name, nested_data;
        EXIT WHEN c_sales%NOTFOUND;
        -- Output information about the main sale record
        DBMS_OUTPUT.PUT_LINE('Sale ID: '         || sale_id
                          || ' Customer: '        || customer_name);
        DBMS_OUTPUT.PUT_LINE('Number of Items: ' || nested_data.COUNT);
        -- Iterate through the elements of the nested table collection
        FOR i IN 1..nested_data.COUNT LOOP
            DBMS_OUTPUT.PUT_LINE(' Item: ' || nested_data(i).item_qty);
        END LOOP;
        DBMS_OUTPUT.PUT_LINE('----------------------------');
    END LOOP;
    -- Clean up resources by closing the cursor
    CLOSE c_sales;
END;
/

Zone-by-Zone Walkthrough

Zone 2 — BEGIN: OPEN and LOOP

The BEGIN section opens the execution section of the block. OPEN c_sales executes the cursor's SELECT statement and positions the cursor before the first row of the result set. The LOOP keyword begins an infinite loop — it has no built-in termination condition. Termination is controlled by the EXIT WHEN statement inside the loop, which fires when the FETCH returns no more rows.

Zone 3 — FETCH, EXIT WHEN %NOTFOUND, DBMS_OUTPUT

FETCH c_sales INTO sale_id, customer_name, nested_data retrieves one row from the cursor's result set into the three declared variables. The variables receive values in the order they appear in both the FETCH INTO list and the cursor's SELECT list — positional correspondence is required. After the FETCH, EXIT WHEN c_sales%NOTFOUND tests the cursor attribute %NOTFOUND. If the FETCH retrieved no row because the result set is exhausted, %NOTFOUND evaluates to TRUE and the loop terminates. If a row was retrieved, %NOTFOUND is FALSE and execution continues.

The two DBMS_OUTPUT.PUT_LINE calls print the parent-row attributes: the first line combines sale_id and customer_name; the second line prints the count of detail elements in the nested_data collection using the COUNT collection method. This gives the reader immediate context — how many items belong to this sale — before the inner loop prints each one.

Zone 4 — Inner FOR LOOP, CLOSE, END

The inner FOR loop — FOR i IN 1..nested_data.COUNT LOOP — iterates from index 1 to the number of elements currently in the nested_data collection. For each index i, nested_data(i).item_qty accesses the item_qty attribute of the i-th element. The dot notation (nested_data(i).item_qty) navigates from the collection variable through the element at index i to the specific attribute — this is the PL/SQL equivalent of the SQL table alias and column reference used in TABLE() queries.

The separator line (PUT_LINE('----------------------------')) after the inner loop visually separates each sale's output block from the next. After the outer loop's EXIT WHEN terminates iteration, CLOSE c_sales releases the cursor's resources — the memory used to hold the result set and the cursor state. Closing the cursor after use is a PL/SQL best practice; unclosed cursors consume session resources until the session ends.

Key PL/SQL Concepts in This Block

Explicit Cursor Pattern — OPEN / FETCH / EXIT / CLOSE

The explicit cursor pattern used in this block has four steps that always appear in the same order. OPEN executes the query and positions the cursor before the first row. FETCH retrieves one row per call and advances the cursor position. EXIT WHEN %NOTFOUND terminates the loop when no more rows are available. CLOSE releases cursor resources. This four-step pattern is the foundation of explicit cursor programming in PL/SQL — deviating from it (for example, forgetting CLOSE, or fetching after %NOTFOUND) produces memory leaks or incorrect results.

The Cursor FOR LOOP — A Concise Alternative

Oracle provides a more concise cursor FOR LOOP construct that handles OPEN, FETCH, and CLOSE automatically, leaving only the loop body for the developer to write. The cursor FOR LOOP is the preferred form in Oracle 23ai for straightforward cursor iteration:

-- Cursor FOR LOOP — Oracle 23ai preferred form for simple iteration
DECLARE
    CURSOR c_sales IS
        SELECT sale_id, customer_name, detail_nest
        FROM   sale_header
        WHERE  sale_id IN (1, 2, 3);
BEGIN
    FOR rec IN c_sales LOOP
        DBMS_OUTPUT.PUT_LINE('Sale ID: '         || rec.sale_id
                          || ' Customer: '        || rec.customer_name);
        DBMS_OUTPUT.PUT_LINE('Number of Items: ' || rec.detail_nest.COUNT);
        FOR i IN 1..rec.detail_nest.COUNT LOOP
            DBMS_OUTPUT.PUT_LINE(' Item: ' || rec.detail_nest(i).item_qty);
        END LOOP;
        DBMS_OUTPUT.PUT_LINE('----------------------------');
    END LOOP;
    -- No explicit OPEN or CLOSE required
END;
/

The cursor FOR LOOP implicitly opens the cursor when the loop begins, fetches one row per iteration into the rec record variable, and closes the cursor when the loop exits — either through exhaustion of rows or an EXIT statement. The rec variable provides attribute access using dot notation (rec.sale_id, rec.customer_name, rec.detail_nest). The three separate variable declarations from the explicit form are replaced by the single implicit record variable rec.

DBMS_OUTPUT.PUT_LINE — Enabling and Using

DBMS_OUTPUT.PUT_LINE writes a line of text to the DBMS_OUTPUT buffer. To see the output in Oracle SQL Developer or SQL*Plus, the server output feature must be enabled before the block runs:

-- Enable DBMS_OUTPUT before running the block
SET SERVEROUTPUT ON;

-- Or in Oracle SQL Developer: View menu → DBMS Output → enable

-- Then run the block
DECLARE
    ...
BEGIN
    ...
    DBMS_OUTPUT.PUT_LINE('Sale ID: ' || sale_id);
    ...
END;
/

Running the Block in Oracle 23ai Tools

SQL*Plus

In SQL*Plus, type SET SERVEROUTPUT ON before pasting or running the block. SQL*Plus is a command-line tool — it executes PL/SQL blocks entered directly or from a script file using the @ command. The forward slash (/) on a line by itself after END; submits the block for execution. SQL*Plus remains the standard tool for script-based deployment in Oracle database administration workflows.