FORALL, SAVE EXCEPTIONS and DML error logging

"""Calls from PL/SQL to the SQL engine for the three copies of three_ways.sql.

What it models: three ways to copy ROWS rows, counted as Oracle's page on
bulk SQL and bulk binding describes them. A cursor FOR loop with an INSERT
inside sends one INSERT per row. FETCH ... BULK COLLECT INTO ... LIMIT with
FORALL sends one FETCH and one INSERT call per batch, and each INSERT call
carries the values of the whole batch; the loop ends on the first FETCH that
returns no row. One INSERT ... SELECT is a single statement: the SQL engine
reads and writes the rows itself, and PL/SQL holds none of them.
It counts calls only: elapsed times depend on the machine, so the script
prints those on yours.
What to edit: ROWS and LIMIT, then run it again.
"""

ROWS = 200_000
LIMIT = 1_000


def batches(rows, limit):
    """Batches a LIMIT loop fills: the full ones, plus one short one if rows remain."""
    full, rest = divmod(rows, limit)
    return full + (1 if rest else 0), rest


n_batches, rest = batches(ROWS, LIMIT)
per_batch = f"{LIMIT:,}" if not rest else f"{LIMIT:,}, last {rest:,}"
ways = [
    ("row by row, one INSERT per row", ROWS, "1"),
    ("BULK COLLECT LIMIT + FORALL", n_batches, per_batch),
    ("one INSERT ... SELECT", 1, f"{ROWS:,}"),
]

print(f"Copy {ROWS:,} rows three ways (LIMIT {LIMIT:,})")
print()
print(f"{'way':<32}{'INSERT calls':>13}{'rows per call':>18}")
for name, calls, rows_per_call in ways:
    print(f"{name:<32}{calls:>13,}{rows_per_call:>18}")
print()
print(f"FORALL way: also {n_batches + 1:,} FETCH calls, {n_batches:,} that fill a batch")
print("and one that returns no row and ends the loop.")
print("INSERT ... SELECT: no FETCH from PL/SQL; the SQL engine reads")
print(f"the {ROWS:,} source rows inside the one statement.")
print(f"Calls saved by FORALL against row by row: {ROWS - n_batches:,} INSERTs")

Copy 200,000 rows three ways (LIMIT 1,000)

way                              INSERT calls     rows per call
row by row, one INSERT per row        200,000                 1
BULK COLLECT LIMIT + FORALL               200             1,000
one INSERT ... SELECT                       1           200,000

FORALL way: also 201 FETCH calls, 200 that fill a batch
and one that returns no row and ends the loop.
INSERT ... SELECT: no FETCH from PL/SQL; the SQL engine reads
the 200,000 source rows inside the one statement.
Calls saved by FORALL against row by row: 199,800 INSERTs

-- Engine: Oracle AI Database 26ai Free, Release 23.26.3.0.0; user LAB in FREEPDB1. Client: SQL*Plus or SQLcl.
-- One copy of 200,000 generated feed rows, done three ways: row by row (a cursor FOR loop with one INSERT per row),
-- in batches of 1,000 (FETCH ... BULK COLLECT ... LIMIT, then one FORALL per batch) and as one INSERT ... SELECT.
-- Each copy prints its elapsed time in hundredths of a second from DBMS_UTILITY.GET_TIME. Times depend on the
-- machine, so compare the three on yours; three_ways_model.py counts the calls each way sends, which do not change.
-- It needs none of the practice tables and drops the four tables it makes.
SET SERVEROUTPUT ON

DROP TABLE IF EXISTS feed_source PURGE;
DROP TABLE IF EXISTS copy_row PURGE;
DROP TABLE IF EXISTS copy_bulk PURGE;
DROP TABLE IF EXISTS copy_set PURGE;

CREATE TABLE feed_source (
  line_no    NUMBER(10)    NOT NULL,
  account_id NUMBER(10)    NOT NULL,
  amount     NUMBER(14, 2) NOT NULL,
  memo       VARCHAR2(60)
);

-- 200,000 rows with values worked out from LEVEL: accounts 1 to 4 in turn, amounts from 1.00 to 100.98.
INSERT INTO feed_source (line_no, account_id, amount, memo)
SELECT LEVEL, MOD(LEVEL - 1, 4) + 1, MOD(LEVEL * 37, 9999) / 100 + 1, 'generated line ' || LEVEL
FROM dual
CONNECT BY LEVEL <= 200000;
COMMIT;

CREATE TABLE copy_row  AS SELECT * FROM feed_source WHERE 1 = 0;
CREATE TABLE copy_bulk AS SELECT * FROM feed_source WHERE 1 = 0;
CREATE TABLE copy_set  AS SELECT * FROM feed_source WHERE 1 = 0;

DECLARE
  CURSOR c_feed IS
    SELECT line_no, account_id, amount, memo FROM feed_source ORDER BY line_no;
  TYPE feed_tab IS TABLE OF c_feed%ROWTYPE;
  v_rows feed_tab;
  t0     NUMBER;
BEGIN
  -- 1. Row by row: 200,000 INSERT statements, one per pass of the loop.
  t0 := DBMS_UTILITY.GET_TIME;
  FOR r IN c_feed LOOP
    INSERT INTO copy_row (line_no, account_id, amount, memo)
    VALUES (r.line_no, r.account_id, r.amount, r.memo);
  END LOOP;
  DBMS_OUTPUT.PUT_LINE('row by row:            ' || (DBMS_UTILITY.GET_TIME - t0) || ' hundredths');

  -- 2. In batches: one FETCH of up to 1,000 rows, then one FORALL that sends the INSERT with all their values.
  t0 := DBMS_UTILITY.GET_TIME;
  OPEN c_feed;
  LOOP
    FETCH c_feed BULK COLLECT INTO v_rows LIMIT 1000;
    EXIT WHEN v_rows.COUNT = 0;   -- the 201st FETCH returns no row and ends the loop
    FORALL i IN 1 .. v_rows.COUNT
      INSERT INTO copy_bulk (line_no, account_id, amount, memo)
      VALUES (v_rows(i).line_no, v_rows(i).account_id, v_rows(i).amount, v_rows(i).memo);
  END LOOP;
  CLOSE c_feed;
  DBMS_OUTPUT.PUT_LINE('BULK COLLECT + FORALL: ' || (DBMS_UTILITY.GET_TIME - t0) || ' hundredths');

  -- 3. One statement: the SQL engine reads and writes every row; PL/SQL holds none of them.
  t0 := DBMS_UTILITY.GET_TIME;
  INSERT INTO copy_set (line_no, account_id, amount, memo)
  SELECT line_no, account_id, amount, memo FROM feed_source;
  DBMS_OUTPUT.PUT_LINE('INSERT ... SELECT:     ' || (DBMS_UTILITY.GET_TIME - t0) || ' hundredths');
  COMMIT;
END;
/
-- Expected: three lines, each with the elapsed hundredths of one copy on this machine.

SELECT (SELECT COUNT(*) FROM copy_row)  AS row_by_row,
       (SELECT COUNT(*) FROM copy_bulk) AS bulk_forall,
       (SELECT COUNT(*) FROM copy_set)  AS set_based
FROM dual;
-- Expected: 200000 in each column: the three ways copied the same rows.

DROP TABLE copy_set PURGE;
DROP TABLE copy_bulk PURGE;
DROP TABLE copy_row PURGE;
DROP TABLE feed_source PURGE;

"""three_ways.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("load/three_ways.sql")

$ sqlplus -s lab@//localhost/FREEPDB1 @load/three_ways.sql
Table dropped.


Table dropped.


Table dropped.


Table dropped.


Table created.


200000 rows created.


Commit complete.


Table created.


Table created.


Table created.

row by row:            354 hundredths
BULK COLLECT + FORALL: 31 hundredths
INSERT ... SELECT:     24 hundredths

PL/SQL procedure successfully completed.


ROW_BY_ROW BULK_FORALL  SET_BASED
---------- ----------- ----------
    200000      200000     200000


Table dropped.


Table dropped.


Table dropped.


Table dropped.

"""The feed of first_failure.sql through a FORALL without SAVE EXCEPTIONS.

What it models: Oracle's rules for a FORALL statement whose DML fails on one
element (the bulk SQL page: unhandled exceptions, and handling FORALL
exceptions immediately). The SQL engine runs the INSERT for elements 1, 2,
3 ... in order and stops at the first entry the ledger refuses. When the
block handles the exception, only that element's change is rolled back:
the rows of the elements before it stay, and SQL%ROWCOUNT counts them. When
nothing handles it, PL/SQL rolls back every row the FORALL inserted. The
elements after the failing one never run in either case.
The ledger's rules: amount <> 0 (ledger_amt_nonzero), an account that
exists (ledger_account_fk; accounts 1 to 4) and a posted_at (NOT NULL).
Each bad entry of this feed breaks one rule only.
What to edit: FEED (fix line 4's amount, or break another line).
"""
from decimal import Decimal

FEED = [  # line, account_id, amount, has a posted_at
    (1, 1, "1200.00", True),
    (2, 3, "-150.00", True),
    (3, 2, "4800.00", True),
    (4, 1, "0.00", True),
    (5, 4, "-320.00", True),
    (6, 1, "-75.50", True),
    (7, 9, "600.00", True),
    (8, 3, "210.00", True),
    (9, 4, "95.00", False),
    (10, 1, "40.00", True),
]
ACCOUNTS = {1, 2, 3, 4}


def refusal(account, amount, dated):
    """The error the ledger raises for one entry, or None when it is accepted."""
    if not dated:
        return "ORA-01400"  # posted_at is NOT NULL
    if Decimal(amount) == 0:
        return "ORA-02290"  # check constraint ledger_amt_nonzero
    if account not in ACCOUNTS:
        return "ORA-02291"  # foreign key ledger_account_fk
    return None


print(f"FORALL ... INSERT of feed-batch-17: {len(FEED)} entries,")
print("no SAVE EXCEPTIONS")
print()
print(f"{'line':>4}{'account':>9}{'amount':>10}  {'posted_at':<10} what happens")
stopped_at = None
inserted = 0
for line, account, amount, dated in FEED:
    if stopped_at is not None:
        result = "never run"
    else:
        error = refusal(account, amount, dated)
        if error:
            stopped_at = line
            result = f"{error}: the FORALL stops"
        else:
            inserted += 1
            result = "inserted"
    date = "set" if dated else "NULL"
    print(f"{line:>4}{account:>9}{amount:>10}  {date:<10} {result}")

bad = [line for line, a, amt, d in FEED if refusal(a, amt, d)]
print()
if stopped_at is None:
    print(f"No entry refused: {inserted} rows inserted, SQL%ROWCOUNT = {inserted}")
else:
    print(f"With a handler:    {inserted} rows stay, SQL%ROWCOUNT = {inserted}")
    print(f"Without a handler: PL/SQL rolls back those {inserted}, 0 stay")
    unseen = [line for line in bad if line != stopped_at]
    print(f"Bad lines in the feed: {', '.join(map(str, bad))};", end=" ")
    print(f"this run reports line {stopped_at} only")
    if unseen:
        print(f"Lines never checked: {', '.join(map(str, unseen))}, until the next run")

FORALL ... INSERT of feed-batch-17: 10 entries,
no SAVE EXCEPTIONS

line  account    amount  posted_at  what happens
   1        1   1200.00  set        inserted
   2        3   -150.00  set        inserted
   3        2   4800.00  set        inserted
   4        1      0.00  set        ORA-02290: the FORALL stops
   5        4   -320.00  set        never run
   6        1    -75.50  set        never run
   7        9    600.00  set        never run
   8        3    210.00  set        never run
   9        4     95.00  NULL       never run
  10        1     40.00  set        never run

With a handler:    3 rows stay, SQL%ROWCOUNT = 3
Without a handler: PL/SQL rolls back those 3, 0 stay
Bad lines in the feed: 4, 7, 9; this run reports line 4 only
Lines never checked: 7, 9, until the next run

-- Engine: Oracle AI Database 26ai Free, Release 23.26.3.0.0; user LAB in FREEPDB1. Client: SQL*Plus or SQLcl.
-- What FORALL does by default when one element fails. The script stages the feed feed-batch-17, 10 ledger entries of
-- which 3 break the ledger's rules (line 4: amount 0, ledger_amt_nonzero; line 7: account 9, ledger_account_fk;
-- line 9: no posted_at, NOT NULL), and loads it with one FORALL twice: without a handler, then with one.
-- Run schema_oracle.sql of practice-schema-and-dialect-basics first; first_failure_model.py prints the same counts.
-- The ROLLBACK and the DROP at the end leave the practice data as they found it.
SET SERVEROUTPUT ON

DROP TABLE IF EXISTS ledger_feed PURGE;

-- The staging table has no constraints, so the bad lines can land here and fail only on their way into the ledger.
CREATE TABLE ledger_feed (
  line_no    NUMBER(4),
  account_id NUMBER(10),
  amount     NUMBER(14, 2),
  posted_at  DATE,
  memo       VARCHAR2(60)
);

INSERT INTO ledger_feed VALUES (1, 1, 1200.00, DATE '2026-04-02', 'feed-batch-17 line 01');
INSERT INTO ledger_feed VALUES (2, 3, -150.00, DATE '2026-04-02', 'feed-batch-17 line 02');
INSERT INTO ledger_feed VALUES (3, 2, 4800.00, DATE '2026-04-02', 'feed-batch-17 line 03');
-- bad: amount 0
INSERT INTO ledger_feed VALUES (4, 1, 0.00, DATE '2026-04-02', 'feed-batch-17 line 04');
INSERT INTO ledger_feed VALUES (5, 4, -320.00, DATE '2026-04-02', 'feed-batch-17 line 05');
INSERT INTO ledger_feed VALUES (6, 1, -75.50, DATE '2026-04-02', 'feed-batch-17 line 06');
-- bad: account 9
INSERT INTO ledger_feed VALUES (7, 9, 600.00, DATE '2026-04-02', 'feed-batch-17 line 07');
INSERT INTO ledger_feed VALUES (8, 3, 210.00, DATE '2026-04-02', 'feed-batch-17 line 08');
-- bad: no date
INSERT INTO ledger_feed VALUES (9, 4, 95.00, NULL, 'feed-batch-17 line 09');
INSERT INTO ledger_feed VALUES (10, 1, 40.00, DATE '2026-04-02', 'feed-batch-17 line 10');
-- the staged lines stay; every change to the ledger below is rolled back at the end
COMMIT;

SELECT COUNT(*) AS ledger_rows FROM ledger;
-- Expected: 5

-- Load 1: FORALL with no handler.
DECLARE
  TYPE feed_tab IS TABLE OF ledger_feed%ROWTYPE;
  v_feed feed_tab;
BEGIN
  SELECT * BULK COLLECT INTO v_feed FROM ledger_feed ORDER BY line_no;
  FORALL i IN 1 .. v_feed.COUNT
    INSERT INTO ledger (account_id, amount, posted_at, memo)
    VALUES (v_feed(i).account_id, v_feed(i).amount, v_feed(i).posted_at, v_feed(i).memo);
END;
/
-- Expected: ORA-02290: check constraint (LAB.LEDGER_AMT_NONZERO) violated, raised by element 4

SELECT COUNT(*) AS ledger_rows FROM ledger;
-- Expected: 5. The exception was not handled, so PL/SQL rolled back the rows of elements 1 to 3 as well (Bulk SQL
-- page, "Unhandled Exceptions in FORALL Statements"), and elements 5 to 10 never ran.

-- Load 2: the same FORALL, with a handler. It only reports, to show what the handler sees; a real load would roll
-- back or raise the error again after logging it, so that its caller sees the failure.
DECLARE
  TYPE feed_tab IS TABLE OF ledger_feed%ROWTYPE;
  v_feed feed_tab;
  v_kept PLS_INTEGER;
BEGIN
  SELECT * BULK COLLECT INTO v_feed FROM ledger_feed ORDER BY line_no;
  FORALL i IN 1 .. v_feed.COUNT
    INSERT INTO ledger (account_id, amount, posted_at, memo)
    VALUES (v_feed(i).account_id, v_feed(i).amount, v_feed(i).posted_at, v_feed(i).memo);
EXCEPTION
  WHEN OTHERS THEN
    v_kept := SQL%ROWCOUNT;   -- copy it first: the next SQL statement would replace it
    DBMS_OUTPUT.PUT_LINE('stopped: ' || SQLERRM);
    DBMS_OUTPUT.PUT_LINE('rows kept: ' || v_kept);
END;
/
-- Expected:
-- stopped: ORA-02290: check constraint (LAB.LEDGER_AMT_NONZERO) violated
-- rows kept: 3

SELECT COUNT(*) AS ledger_rows FROM ledger;
-- Expected: 8. The handled error rolled back element 4 only; the rows of elements 1 to 3 wait in the open
-- transaction (Bulk SQL page, "Handling FORALL Exceptions Immediately"), and elements 5 to 10 never ran.

ROLLBACK;
DROP TABLE ledger_feed PURGE;
SELECT COUNT(*) AS ledger_rows FROM ledger;
-- Expected: 5

"""first_failure.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("errors/first_failure.sql", errors=1)

$ sqlplus -s lab@//localhost/FREEPDB1 @errors/first_failure.sql
Table dropped.


Table created.


1 row created.


1 row created.


1 row created.


1 row created.


1 row created.


1 row created.


1 row created.


1 row created.


1 row created.


1 row created.


Commit complete.


LEDGER_ROWS
-----------
          5

DECLARE
*
ERROR at line 1:
ORA-02290: check constraint (LAB.LEDGER_AMT_NONZERO) violated
ORA-06512: at line 6
Help: https://docs.oracle.com/error-help/db/ora-02290/



LEDGER_ROWS
-----------
          5

stopped: ORA-02290: check constraint (LAB.LEDGER_AMT_NONZERO) violated
rows kept: 3

PL/SQL procedure successfully completed.


LEDGER_ROWS
-----------
          8


Rollback complete.


Table dropped.


LEDGER_ROWS
-----------
          5