Explicit cursors and cursor FOR loops

-- Engine: Oracle AI Database 26ai Free, Release 23.26.3.0.0; user LAB in FREEPDB1. Client: SQL*Plus or SQLcl.
-- An explicit cursor over account 2 of the practice ledger, with a parameter and a default value.
-- Runs on the practice schema (set up by schema_oracle.sql of practice-schema-and-dialect-basics first). The script
-- inserts eight more ledger rows for account 2 so there are nine to fetch, opens a cursor with its default parameter,
-- loops with FETCH ... INTO and prints %ROWCOUNT, %FOUND and %NOTFOUND after each pass. ROLLBACK at the end leaves
-- the data as it was. cursor_model.py in examples/model prints the same attribute values from the documented rules.
SET SERVEROUTPUT ON
SET LINESIZE 120

-- Add eight more ledger entries for account 2 (entry_id and ledger_entries_seq are GENERATED ALWAYS).
INSERT INTO ledger (account_id, amount, posted_at, memo)
SELECT 2,
       CASE MOD(LEVEL, 2) WHEN 0 THEN LEVEL * 100 ELSE -LEVEL * 50 END,
       TIMESTAMP '2026-09-01 09:00:00' + NUMTODSINTERVAL(LEVEL, 'DAY'),
       'extra ledger line ' || LEVEL
FROM dual CONNECT BY LEVEL <= 8;

DECLARE
  CURSOR c_ledger (p_account INT DEFAULT 2) IS
    SELECT entry_id, amount, memo
    FROM ledger
    WHERE account_id = p_account
    ORDER BY posted_at, entry_id;
  v_entry  ledger.entry_id%TYPE;
  v_amount ledger.amount%TYPE;
  v_memo   ledger.memo%TYPE;
BEGIN
  OPEN c_ledger;                                                 -- the default binds p_account to 2
  LOOP
    FETCH c_ledger INTO v_entry, v_amount, v_memo;
    EXIT WHEN c_ledger%NOTFOUND;                                 -- NOTFOUND is TRUE only after the last FETCH
    DBMS_OUTPUT.PUT_LINE(
      'count=' || c_ledger%ROWCOUNT ||
      ' found=' || CASE WHEN c_ledger%FOUND THEN 'TRUE' ELSE 'FALSE' END ||
      ' notfound=' || CASE WHEN c_ledger%NOTFOUND THEN 'TRUE' ELSE 'FALSE' END ||
      ' entry=' || v_entry ||
      ' amount=' || v_amount
    );
  END LOOP;
  DBMS_OUTPUT.PUT_LINE('rows fetched: ' || c_ledger%ROWCOUNT);   -- 9 after the loop ends
  CLOSE c_ledger;
END;
/
-- Expected: nine lines report count 1 through 9 with found=TRUE and notfound=FALSE, then "rows fetched: 9".

-- removes the eight extra rows this script inserted
ROLLBACK;

"""account_cursor.sql in the lab: SQL*Plus runs it as LAB on a freshly loaded practice schema."""

from oracle_session import Lab

with Lab() as lab:
    lab.sqlplus("cursor/account_cursor.sql")

$ sqlplus -s lab@//localhost/FREEPDB1 @cursor/account_cursor.sql
8 rows created.

count=1 found=TRUE notfound=FALSE entry=2 amount=18000
count=2 found=TRUE notfound=FALSE entry=6 amount=-50
count=3 found=TRUE notfound=FALSE entry=7 amount=200
count=4 found=TRUE notfound=FALSE entry=8 amount=-150
count=5 found=TRUE notfound=FALSE entry=9 amount=400
count=6 found=TRUE notfound=FALSE entry=10 amount=-250
count=7 found=TRUE notfound=FALSE entry=11 amount=600
count=8 found=TRUE notfound=FALSE entry=12 amount=-350
count=9 found=TRUE notfound=FALSE entry=13 amount=800
rows fetched: 9

PL/SQL procedure successfully completed.


Rollback complete.

"""The documented attribute rules of an explicit cursor, and fetch counts of two loop shapes.

A model, not a run: it applies the attribute rules of Oracle's named-cursor-attribute page to
the rows the lesson's scripts hold, so the numbers the text quotes stay checkable without an
Oracle server. For the cursor FOR loop at PLSQL_OPTIMIZE_LEVEL 2 it assumes an array fetch of
ARRAY_FETCH_LIMIT rows per call; no Oracle page states that size, and fetch_counts.sql reads
the real fetch calls from V$SQL. Edit ACCOUNT_LEDGER_ROWS to add or remove rows for account 2,
LARGE_ROWS_FOR for the size of the FOR loop's result, or ARRAY_FETCH_LIMIT for another batch.
"""

from __future__ import annotations

ACCOUNT_LEDGER_ROWS = 9           # account 2's rows after account_cursor.sql inserts eight extra entries
ARRAY_FETCH_LIMIT = 100           # the model's assumed batch of a cursor FOR loop at PLSQL_OPTIMIZE_LEVEL 2
LARGE_ROWS_FOR = 1_001            # fetch_counts.sql: the cursor FOR loop reads 1,001 rows
LARGE_ROWS_EXPLICIT = 1_002       # fetch_counts.sql: the explicit FETCH loop reads 1,002 rows


def attributes(step: str, is_open: bool, rowcount: int | None, last_returned_row: bool | None) -> dict:
    """The four values an open or closed explicit cursor's attributes carry at one step."""
    if not is_open:
        if step == "before OPEN":
            return {"step": step, "%ISOPEN": "FALSE", "%FOUND": "INVALID_CURSOR", "%NOTFOUND": "INVALID_CURSOR", "%ROWCOUNT": "INVALID_CURSOR"}
        return {"step": step, "%ISOPEN": "FALSE", "%FOUND": "INVALID_CURSOR", "%NOTFOUND": "INVALID_CURSOR", "%ROWCOUNT": "INVALID_CURSOR"}
    if last_returned_row is None:                # the cursor is open but no fetch has run yet
        return {"step": step, "%ISOPEN": "TRUE", "%FOUND": "NULL", "%NOTFOUND": "NULL", "%ROWCOUNT": str(rowcount or 0)}
    return {
        "step": step,
        "%ISOPEN": "TRUE",
        "%FOUND": "TRUE" if last_returned_row else "FALSE",
        "%NOTFOUND": "FALSE" if last_returned_row else "TRUE",
        "%ROWCOUNT": str(rowcount),
    }


def table_for_account(n_rows: int) -> list[dict]:
    """The attribute table of an explicit FETCH loop over n_rows: before OPEN, after each FETCH, after CLOSE."""
    steps = [attributes("before OPEN", is_open=False, rowcount=None, last_returned_row=None)]
    steps.append(attributes("after OPEN", is_open=True, rowcount=0, last_returned_row=None))
    for k in range(1, n_rows + 1):
        steps.append(attributes(f"after FETCH {k}", is_open=True, rowcount=k, last_returned_row=True))
    steps.append(attributes(f"after FETCH {n_rows + 1}", is_open=True, rowcount=n_rows, last_returned_row=False))
    steps.append(attributes("after CLOSE", is_open=False, rowcount=None, last_returned_row=None))
    return steps


def print_table(rows: list[dict]) -> None:
    widths = {k: max(len(k), max(len(r[k]) for r in rows)) for k in rows[0]}
    header = "  ".join(k.ljust(widths[k]) for k in rows[0])
    print(header)
    print("  ".join("-" * widths[k] for k in rows[0]))
    for r in rows:
        print("  ".join(r[k].ljust(widths[k]) for k in r))


def fetch_calls_explicit(n_rows: int) -> int:
    """One FETCH per row returns a row; one more FETCH returns none and ends the loop: n + 1."""
    return n_rows + 1


def fetch_calls_for_loop(n_rows: int, limit: int) -> int:
    """The model's array fetch: full batches of limit rows, then one call that returns the rest or nothing."""
    return n_rows // limit + 1


print("Attributes step by step, for a FETCH loop over account 2's", ACCOUNT_LEDGER_ROWS, "rows:")
print_table(table_for_account(ACCOUNT_LEDGER_ROWS))
print()
print("Fetch calls (one call means one round trip across the PL/SQL and SQL engines):")
print(f"  explicit FETCH loop, {ACCOUNT_LEDGER_ROWS} rows: {fetch_calls_explicit(ACCOUNT_LEDGER_ROWS)}")
print(f"  cursor FOR loop at level 2, {ACCOUNT_LEDGER_ROWS} rows, LIMIT {ARRAY_FETCH_LIMIT}: "
      f"{fetch_calls_for_loop(ACCOUNT_LEDGER_ROWS, ARRAY_FETCH_LIMIT)}")
print(f"  explicit FETCH loop, {LARGE_ROWS_EXPLICIT:,} rows: {fetch_calls_explicit(LARGE_ROWS_EXPLICIT):,}")
print(f"  cursor FOR loop at level 2, {LARGE_ROWS_FOR:,} rows, LIMIT {ARRAY_FETCH_LIMIT}: "
      f"{fetch_calls_for_loop(LARGE_ROWS_FOR, ARRAY_FETCH_LIMIT)}")
print(f"  cursor FOR loop at level 1 (no array fetch), {LARGE_ROWS_FOR:,} rows: "
      f"{fetch_calls_explicit(LARGE_ROWS_FOR):,}")

Attributes step by step, for a FETCH loop over account 2's 9 rows:
step            %ISOPEN  %FOUND          %NOTFOUND       %ROWCOUNT     
--------------  -------  --------------  --------------  --------------
before OPEN     FALSE    INVALID_CURSOR  INVALID_CURSOR  INVALID_CURSOR
after OPEN      TRUE     NULL            NULL            0             
after FETCH 1   TRUE     TRUE            FALSE           1             
after FETCH 2   TRUE     TRUE            FALSE           2             
after FETCH 3   TRUE     TRUE            FALSE           3             
after FETCH 4   TRUE     TRUE            FALSE           4             
after FETCH 5   TRUE     TRUE            FALSE           5             
after FETCH 6   TRUE     TRUE            FALSE           6             
after FETCH 7   TRUE     TRUE            FALSE           7             
after FETCH 8   TRUE     TRUE            FALSE           8             
after FETCH 9   TRUE     TRUE            FALSE           9             
after FETCH 10  TRUE     FALSE           TRUE            9             
after CLOSE     FALSE    INVALID_CURSOR  INVALID_CURSOR  INVALID_CURSOR

Fetch calls (one call means one round trip across the PL/SQL and SQL engines):
  explicit FETCH loop, 9 rows: 10
  cursor FOR loop at level 2, 9 rows, LIMIT 100: 1
  explicit FETCH loop, 1,002 rows: 1,003
  cursor FOR loop at level 2, 1,001 rows, LIMIT 100: 11
  cursor FOR loop at level 1 (no array fetch), 1,001 rows: 1,002

-- Engine: Oracle AI Database 26ai Free, Release 23.26.3.0.0; user LAB in FREEPDB1. Client: SQL*Plus or SQLcl.
-- A FETCH after CLOSE raises INVALID_CURSOR (ORA-01001). Oracle's cursors overview page states that an attribute
-- or FETCH on a closed cursor raises INVALID_CURSOR, and ora-01001.txt names the code. Runs on the practice schema
-- (set up by schema_oracle.sql of practice-schema-and-dialect-basics first); nothing is changed, nothing to roll back.
SET SERVEROUTPUT ON
SET LINESIZE 120

DECLARE
  CURSOR c_savings IS
    SELECT account_id, balance
    FROM accounts
    WHERE kind = 'savings'
    ORDER BY account_id;
  v_account accounts.account_id%TYPE;
  v_balance accounts.balance%TYPE;
BEGIN
  OPEN c_savings;
  FETCH c_savings INTO v_account, v_balance;
  DBMS_OUTPUT.PUT_LINE('open: account ' || v_account || ' at ' || v_balance);
  CLOSE c_savings;                                       -- the cursor is gone from this line on
  BEGIN
    FETCH c_savings INTO v_account, v_balance;           -- raises INVALID_CURSOR, caught below
  EXCEPTION
    WHEN INVALID_CURSOR THEN
      DBMS_OUTPUT.PUT_LINE('fetch after close: SQLCODE ' || SQLCODE || ', ' || SUBSTR(SQLERRM, 1, 60));
  END;
END;
/
-- Expected: a line about the first savings account, then "fetch after close: SQLCODE -1001, ORA-01001: ...".

"""closed_cursor.sql in the lab: SQL*Plus runs it as LAB on a freshly loaded practice schema."""

from oracle_session import Lab

with Lab() as lab:
    lab.sqlplus("cursor/closed_cursor.sql")

$ sqlplus -s lab@//localhost/FREEPDB1 @cursor/closed_cursor.sql
open: account 1 at 2500
fetch after close: SQLCODE -1001, ORA-01001: cursor number is invalid or does not exist

PL/SQL procedure successfully completed.

-- Engine: Oracle AI Database 26ai Free, Release 23.26.3.0.0; user LAB in FREEPDB1. Client: SQL*Plus or SQLcl.
-- V$SQL counts the fetch calls each loop made. LAB holds DB_DEVELOPER_ROLE, which reads V$STATNAME and V$PARAMETER
-- but not V$SQL (Oracle Security Guide, the role's object privileges), so grant that one view once as SYS AS SYSDBA:
--   ALTER SESSION SET CONTAINER = FREEPDB1;
--   GRANT READ ON SYS.V_$SQL TO LAB;
-- The script creates a scratch table of 1,002 rows, runs a cursor FOR loop over 1,001 of them and an explicit FETCH
-- loop over all 1,002, and reads EXECUTIONS, FETCHES and ROWS_PROCESSED from V$SQL for each query. Then it sets
-- PLSQL_OPTIMIZE_LEVEL to 1 for the session, so the next anonymous block is compiled at level 1, and repeats the FOR
-- loop. Each query carries a string literal in its select list as a tag: the text of the statement keeps a literal,
-- while a comment would depend on how PL/SQL hands the statement's text to the SQL engine. V$SQL adds up the runs of
-- a statement since it was loaded, so the lookups divide by EXECUTIONS and print the counts of one run.
-- cursor_model.py in examples/model assumes about 100 rows per fetch call for the cursor FOR loop at level 2 and
-- prints 11 and 1,003, and close to 1,002 for the FOR loop at level 1; the FETCHES this script prints are the real
-- counts, to set against the model.
SET SERVEROUTPUT ON
SET LINESIZE 160
COLUMN sql_text FORMAT A60

DROP TABLE IF EXISTS fc_rows PURGE;
CREATE TABLE fc_rows (id NUMBER(6) PRIMARY KEY, amount NUMBER(10, 2) NOT NULL);
INSERT INTO fc_rows (id, amount)
SELECT LEVEL, LEVEL * 10 FROM dual CONNECT BY LEVEL <= 1002;
COMMIT;

-- Level 2 is the default of PLSQL_OPTIMIZE_LEVEL (Oracle Database Reference). The model assumes the cursor FOR loop
-- reads about 100 rows per fetch call, so 1,001 rows would take about 11 calls; the V$SQL query below shows the count.
BEGIN
  FOR r IN (SELECT 'fc_for_level2' AS tag, id, amount FROM fc_rows WHERE id <= 1001 ORDER BY id) LOOP
    NULL;                                                 -- no body, so only the loop's work is measured
  END LOOP;
END;
/

DECLARE
  CURSOR c IS SELECT 'fc_explicit_level2' AS tag, id, amount FROM fc_rows WHERE id <= 1002 ORDER BY id;
  v_tag    VARCHAR2(20);
  v_id     fc_rows.id%TYPE;
  v_amount fc_rows.amount%TYPE;
BEGIN
  OPEN c;
  LOOP
    FETCH c INTO v_tag, v_id, v_amount;
    EXIT WHEN c%NOTFOUND;
    NULL;
  END LOOP;
  CLOSE c;
END;
/

-- command_type 3 keeps the SELECT statements, because the anonymous blocks hold the tags too; the NOT LIKE leaves out
-- this query, whose own text also holds them.
SELECT substr(sql_text, 1, 60) AS sql_text, executions,
       ROUND(fetches / NULLIF(executions, 0)) AS fetches_per_run,
       ROUND(rows_processed / NULLIF(executions, 0)) AS rows_per_run
FROM v$sql
WHERE command_type = 3
AND   sql_text NOT LIKE '%v$sql%'
AND   (sql_text LIKE '%''fc_for_level2''%' OR sql_text LIKE '%''fc_explicit_level2''%');
-- Expected, if the cursor FOR loop fetches about 100 rows a call as the model assumes: fetches_per_run 11 for the
-- fc_for_level2 query and 1003 for fc_explicit_level2, rows_per_run 1001 and 1002.

ALTER SESSION SET PLSQL_OPTIMIZE_LEVEL = 1;

BEGIN
  FOR r IN (SELECT 'fc_for_level1' AS tag, id, amount FROM fc_rows WHERE id <= 1001 ORDER BY id) LOOP
    NULL;
  END LOOP;
END;
/

SELECT substr(sql_text, 1, 60) AS sql_text, executions,
       ROUND(fetches / NULLIF(executions, 0)) AS fetches_per_run,
       ROUND(rows_processed / NULLIF(executions, 0)) AS rows_per_run
FROM v$sql
WHERE command_type = 3
AND   sql_text NOT LIKE '%v$sql%'
AND   sql_text LIKE '%''fc_for_level1''%';
-- Expected, if level 1 leaves out the array fetch as the model assumes: fetches_per_run close to 1002 (one per row
-- and a final empty fetch), rows_per_run 1001.

-- back to the default before you run the next script
ALTER SESSION SET PLSQL_OPTIMIZE_LEVEL = 2;
DROP TABLE fc_rows PURGE;

"""fetch_counts.sql in the lab as LAB, after the one grant its header names."""

from oracle_session import Lab

with Lab() as lab:
    lab.sysdba("GRANT READ ON SYS.V_$SQL TO lab;", note="once, as SYS in FREEPDB1")
    lab.sqlplus("fetches/fetch_counts.sql")

-- once, as SYS in FREEPDB1
$ sqlplus -s / as sysdba <<'SQL'
ALTER SESSION SET CONTAINER = FREEPDB1;
GRANT READ ON SYS.V_$SQL TO lab;
SQL
Session altered.


Grant succeeded.

$ sqlplus -s lab@//localhost/FREEPDB1 @fetches/fetch_counts.sql
Table dropped.


Table created.


1002 rows created.


Commit complete.


PL/SQL procedure successfully completed.


PL/SQL procedure successfully completed.


SQL_TEXT                                                     EXECUTIONS FETCHES_PER_RUN ROWS_PER_RUN
------------------------------------------------------------ ---------- --------------- ------------
SELECT 'fc_for_level2' AS TAG, ID, AMOUNT FROM FC_ROWS WHERE          1              11         1001
SELECT 'fc_explicit_level2' AS TAG, ID, AMOUNT FROM FC_ROWS           1            1003         1002


Session altered.


PL/SQL procedure successfully completed.


SQL_TEXT                                                     EXECUTIONS FETCHES_PER_RUN ROWS_PER_RUN
------------------------------------------------------------ ---------- --------------- ------------
SELECT 'fc_for_level1' AS TAG, ID, AMOUNT FROM FC_ROWS WHERE          1            1002         1001


Session altered.


Table dropped.

-- Engine: Oracle AI Database 26ai Free, Release 23.26.3.0.0; user LAB in FREEPDB1. Client: SQL*Plus or SQLcl.
-- A FOR UPDATE cursor locks the result rows at OPEN and WHERE CURRENT OF changes the row the cursor last fetched.
-- Oracle's transaction-processing page states that the rows unlock on COMMIT or ROLLBACK and that a fetch after
-- a COMMIT raises ORA-01002, "fetch out of sequence" (ora-01002.txt). This script shows a safe loop that raises each
-- savings balance by 50.00, the COMMIT-inside-the-loop bug that breaks the next FETCH, and the fix. ROLLBACK at the
-- end leaves the lab's data unchanged. Runs on the practice schema (set up by schema_oracle.sql of
-- practice-schema-and-dialect-basics first).
SET SERVEROUTPUT ON
SET LINESIZE 120

-- The safe loop: FOR UPDATE at OPEN, WHERE CURRENT OF for each change, one COMMIT (or ROLLBACK) outside the loop.
DECLARE
  CURSOR c_savings IS
    SELECT account_id, balance
    FROM accounts
    WHERE kind = 'savings'
    ORDER BY account_id
    FOR UPDATE OF balance;
  v_rise accounts.balance%TYPE := 50.00;
  v_account accounts.account_id%TYPE;
  v_balance accounts.balance%TYPE;
BEGIN
  OPEN c_savings;                                              -- locks every row of the result at this point
  LOOP
    FETCH c_savings INTO v_account, v_balance;
    EXIT WHEN c_savings%NOTFOUND;
    UPDATE accounts
    SET balance = balance + v_rise
    WHERE CURRENT OF c_savings;                                -- changes the row this cursor last fetched
    DBMS_OUTPUT.PUT_LINE('safe loop: account ' || v_account || ' now at ' || (v_balance + v_rise));
  END LOOP;
  CLOSE c_savings;
END;
/
-- Expected: three lines showing accounts 1, 3 and 4 raised to 2550, 810.25 and 41300.5.

-- undo the raise before the next block
ROLLBACK;

-- The bug: a COMMIT inside the loop releases the locks, so the next FETCH raises ORA-01002.
DECLARE
  CURSOR c_savings IS
    SELECT account_id, balance
    FROM accounts
    WHERE kind = 'savings'
    ORDER BY account_id
    FOR UPDATE OF balance;
  v_rise accounts.balance%TYPE := 50.00;
  v_account accounts.account_id%TYPE;
  v_balance accounts.balance%TYPE;
BEGIN
  OPEN c_savings;
  LOOP
    FETCH c_savings INTO v_account, v_balance;                 -- raises ORA-01002 on the second pass
    EXIT WHEN c_savings%NOTFOUND;
    UPDATE accounts
    SET balance = balance + v_rise
    WHERE CURRENT OF c_savings;
    COMMIT;                                                    -- releases the row locks here
    DBMS_OUTPUT.PUT_LINE('bug loop: account ' || v_account || ' now at ' || (v_balance + v_rise));
  END LOOP;
  CLOSE c_savings;
EXCEPTION
  WHEN OTHERS THEN
    DBMS_OUTPUT.PUT_LINE('caught ' || SQLCODE || ': ' || SUBSTR(SQLERRM, 1, 60));
    IF c_savings%ISOPEN THEN CLOSE c_savings; END IF;
END;
/
-- Expected: one line for account 1, then "caught -1002: ORA-01002: fetch out of sequence".

-- The bug block's COMMIT made its one change, account 1 at 2550, permanent, so a ROLLBACK cannot undo it.
-- Take it back with an UPDATE of its own, and commit that, so the next block starts from the practice data.
UPDATE accounts SET balance = balance - 50.00 WHERE account_id = 1;
COMMIT;

-- The fix: keep the COMMIT out of the loop and run it once, after CLOSE, when every row has been changed.
DECLARE
  CURSOR c_savings IS
    SELECT account_id, balance
    FROM accounts
    WHERE kind = 'savings'
    ORDER BY account_id
    FOR UPDATE OF balance;
  v_rise    accounts.balance%TYPE := 50.00;
  v_account accounts.account_id%TYPE;
  v_balance accounts.balance%TYPE;
BEGIN
  OPEN c_savings;
  LOOP
    FETCH c_savings INTO v_account, v_balance;
    EXIT WHEN c_savings%NOTFOUND;
    UPDATE accounts
    SET balance = balance + v_rise
    WHERE CURRENT OF c_savings;
    DBMS_OUTPUT.PUT_LINE('fix loop: account ' || v_account || ' now at ' || (v_balance + v_rise));
  END LOOP;
  CLOSE c_savings;
  -- COMMIT;                                                   -- in production, commit here; the script ends with ROLLBACK
END;
/
-- Expected: the same three lines the safe loop printed.

-- leaves the practice schema as it was
ROLLBACK;

"""for_update.sql in the lab: SQL*Plus runs it as LAB on a freshly loaded practice schema."""

from oracle_session import Lab

with Lab() as lab:
    lab.sqlplus("locking/for_update.sql")

$ sqlplus -s lab@//localhost/FREEPDB1 @locking/for_update.sql
safe loop: account 1 now at 2550
safe loop: account 3 now at 810.25
safe loop: account 4 now at 41300.5

PL/SQL procedure successfully completed.


Rollback complete.

bug loop: account 1 now at 2550
caught -1002: ORA-01002: fetch out of sequence

PL/SQL procedure successfully completed.


1 row updated.


Commit complete.

fix loop: account 1 now at 2550
fix loop: account 3 now at 810.25
fix loop: account 4 now at 41300.5

PL/SQL procedure successfully completed.


Rollback complete.