PL/SQL exceptions and how they propagate

-- Engine: Oracle AI Database 26ai Free, Release 23.26.3.0.0; user LAB in FREEPDB1. Client: SQL*Plus or SQLcl.
-- Handlers in one block. Order 3 (900.00) split into 0 parts raises ZERO_DIVIDE: the rest of the block is skipped and
-- the ZERO_DIVIDE handler prints SQLCODE and SQLERRM. The second block puts the division in an inner block with its
-- own handler, so the loop goes on after the error. The third block shows the order Oracle demands. No data changes.
SET SERVEROUTPUT ON

DECLARE
  v_total NUMBER(10, 2);
  v_parts PLS_INTEGER := 0;
BEGIN
  SELECT o.qty * s.unit_price INTO v_total
    FROM orders o JOIN stock s ON s.sku = o.sku
   WHERE o.order_id = 3;
  DBMS_OUTPUT.PUT_LINE('Total: ' || TO_CHAR(v_total, 'FM999990.00'));
  DBMS_OUTPUT.PUT_LINE('Each: ' || TO_CHAR(v_total / v_parts, 'FM999990.00'));   -- raises ZERO_DIVIDE
  DBMS_OUTPUT.PUT_LINE('never printed');
EXCEPTION
  WHEN NO_DATA_FOUND THEN
    DBMS_OUTPUT.PUT_LINE('No such order');
  WHEN ZERO_DIVIDE THEN
    DBMS_OUTPUT.PUT_LINE('Handled: SQLCODE ' || SQLCODE || ', ' || SQLERRM);
  WHEN OTHERS THEN                          -- every other exception; it must come last
    DBMS_OUTPUT.PUT_LINE('Unexpected: ' || SQLERRM);
    RAISE;                                  -- report it, then raise it again for the client
END;
/
-- Expected (Predefined Exceptions page: ZERO_DIVIDE is ORA-01476 with code -1476; text from the ORA-01476 page):
--   Total: 900.00
--   Handled: SQLCODE -1476, ORA-01476: divisor is equal to zero

DECLARE
  v_total NUMBER(10, 2) := 900;
BEGIN
  FOR v_parts IN REVERSE 0 .. 3 LOOP
    BEGIN                                   -- inner block: its handler lets the loop go on
      DBMS_OUTPUT.PUT_LINE('split in ' || v_parts || ': ' || TO_CHAR(v_total / v_parts, 'FM999990.00'));
    EXCEPTION
      WHEN ZERO_DIVIDE THEN
        DBMS_OUTPUT.PUT_LINE('split in ' || v_parts || ': skipped');
    END;
  END LOOP;
  DBMS_OUTPUT.PUT_LINE('loop finished');
END;
/
-- Expected (Continuing Execution After Handling Exceptions page: a handler never returns to its own block, so the
-- division gets a block of its own):
--   split in 3: 300.00
--   split in 2: 450.00
--   split in 1: 900.00
--   split in 0: skipped
--   loop finished

BEGIN
  DBMS_OUTPUT.PUT_LINE(900 / 0);
EXCEPTION
  WHEN OTHERS THEN
    DBMS_OUTPUT.PUT_LINE('caught by OTHERS');
  WHEN ZERO_DIVIDE THEN
    DBMS_OUTPUT.PUT_LINE('caught by ZERO_DIVIDE');
END;
/
-- Expected (error help PLS-00370): the block does not compile, so nothing runs:
--   PLS-00370: OTHERS handler must be last among the exception handlers of a block

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

$ sqlplus -s lab@//localhost/FREEPDB1 @handlers/handlers.sql
Total: 900.00
Handled: SQLCODE -1476, ORA-01476: divisor is equal to zero

PL/SQL procedure successfully completed.

split in 3: 300.00
split in 2: 450.00
split in 1: 900.00
split in 0: skipped
loop finished

PL/SQL procedure successfully completed.

  WHEN OTHERS THEN
  *
ERROR at line 4:
ORA-06550: line 4, column 3:
PLS-00370: OTHERS handler must be last among the exception handlers of a block
ORA-06550: line 0, column 0:
PL/SQL: Compilation unit analysis terminated
Help: https://docs.oracle.com/error-help/db/ora-06550/

-- Engine: Oracle AI Database 26ai Free, Release 23.26.3.0.0; user LAB in FREEPDB1. Client: SQL*Plus or SQLcl.
-- One exception climbing from an inner block to the procedure around it, then to the caller and the client.
-- print_instalment splits an order's total into equal parts: order 3 is 5 x TEA-250 at 180.00 = 900.00, and order 7
-- does not exist. examples/model/propagation_model.py prints the same path for every call. The script changes no
-- data and drops its procedure at the end.
SET SERVEROUTPUT ON

CREATE OR REPLACE PROCEDURE print_instalment (p_order_id IN NUMBER, p_parts IN NUMBER)
  AUTHID DEFINER
IS
BEGIN
  DECLARE                                   -- the inner block: the lookup and the division
    v_total NUMBER(10, 2);
  BEGIN
    SELECT o.qty * s.unit_price INTO v_total
      FROM orders o JOIN stock s ON s.sku = o.sku
     WHERE o.order_id = p_order_id;         -- order 7: NO_DATA_FOUND
    DBMS_OUTPUT.PUT_LINE('Order ' || p_order_id || ': ' || p_parts || ' parts of '
                         || TO_CHAR(v_total / p_parts, 'FM999990.00'));   -- 0 parts: ZERO_DIVIDE
  EXCEPTION
    WHEN VALUE_ERROR THEN
      DBMS_OUTPUT.PUT_LINE('inner block: VALUE_ERROR');
  END;
  DBMS_OUTPUT.PUT_LINE('Order ' || p_order_id || ': done');
EXCEPTION
  WHEN ZERO_DIVIDE THEN
    DBMS_OUTPUT.PUT_LINE('print_instalment: ZERO_DIVIDE, SQLCODE ' || SQLCODE);
END print_instalment;
/

-- The first caller handles NO_DATA_FOUND.
BEGIN
  print_instalment(3, 3);
  print_instalment(3, 0);
  print_instalment(7, 2);
  DBMS_OUTPUT.PUT_LINE('caller: all three calls done');   -- skipped: the third call fails
EXCEPTION
  WHEN NO_DATA_FOUND THEN
    DBMS_OUTPUT.PUT_LINE('caller: NO_DATA_FOUND, SQLCODE ' || SQLCODE);
END;
/
-- Expected (Overview of Exception Handling and Exception Propagation pages; codes from Predefined Exceptions):
--   Order 3: 3 parts of 300.00
--   Order 3: done
--   print_instalment: ZERO_DIVIDE, SQLCODE -1476
--   caller: NO_DATA_FOUND, SQLCODE 100

-- The second caller handles nothing.
BEGIN
  print_instalment(7, 2);
END;
/
-- Expected: no level handles NO_DATA_FOUND, so the client prints the error stack, as the Exception Propagation
-- page shows for an unhandled exception (line 8 is the SELECT INTO of the stored procedure):
--   ORA-01403: no data found
--   ORA-06512: at "LAB.PRINT_INSTALMENT", line 8
--   ORA-06512: at line 2

DROP PROCEDURE print_instalment;

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

$ sqlplus -s lab@//localhost/FREEPDB1 @propagation/propagation.sql
Procedure created.

Order 3: 3 parts of 300.00
Order 3: done
print_instalment: ZERO_DIVIDE, SQLCODE -1476
caller: NO_DATA_FOUND, SQLCODE 100

PL/SQL procedure successfully completed.

BEGIN
*
ERROR at line 1:
ORA-01403: no data found
ORA-06512: at "LAB.PRINT_INSTALMENT", line 8
ORA-06512: at line 2
Help: https://docs.oracle.com/error-help/db/ora-01403/



Procedure dropped.

"""Which handler catches an exception: Oracle's propagation rules, modelled.

The model replays the two caller blocks of propagation.sql on the practice
orders. print_instalment looks up an order and divides its total into parts
inside an inner block. The levels an exception can reach, innermost first:
the inner block, the body of print_instalment, the caller, then the client.

The rules (PL/SQL Language Reference 26ai, Overview of Exception Handling
and Exception Propagation):
1. A failing statement stops its block. The block's handlers are read from
   the top; the first WHEN that names the exception, or OTHERS, runs, and
   control then goes to the end of that block, never back.
2. With no such handler, the exception leaves the block and is raised
   again in the enclosing block, then in the caller, at the call.
3. If no level handles it, the client (SQL*Plus or SQLcl) reports it.

Edit HANDLERS or the calls and run it again: give the inner block a
NO_DATA_FOUND handler, or take the caller's handler away.
"""
from decimal import Decimal

ORDER_TOTAL = {1: Decimal("360.00"), 2: Decimal("420.00"), 3: Decimal("900.00")}
ERRORS = {  # name: (Oracle error, SQLCODE), from the Predefined Exceptions page
    "NO_DATA_FOUND": ("ORA-01403", 100),
    "ZERO_DIVIDE": ("ORA-01476", -1476),
    "VALUE_ERROR": ("ORA-06502", -6502),
}
HANDLERS = {
    "inner block": ["VALUE_ERROR"],
    "print_instalment": ["ZERO_DIVIDE"],
    "caller": ["NO_DATA_FOUND"],
}
FIRST_CALLER = [(3, 3), (3, 0), (7, 2)]   # the first caller block
SECOND_CALLER = [(7, 2)]                   # the second one, no handler


def catcher(level, handlers, name):
    """The handler of one level that catches the exception, or None."""
    for h in handlers.get(level, []):
        if h in (name, "OTHERS"):
            return h
    return None


def instalment(order_id, parts):
    """The inner block: raises an exception name, or returns the text."""
    if order_id not in ORDER_TOTAL:
        raise LookupError("NO_DATA_FOUND")
    if parts == 0:
        raise LookupError("ZERO_DIVIDE")
    each = (ORDER_TOTAL[order_id] / parts).quantize(Decimal("0.01"))
    return f"Order {order_id}: {parts} parts of {each}"


def run_caller(title, calls, handlers):
    """Runs one caller block; returns False if the client saw an error."""
    print(title)
    for order_id, parts in calls:
        print(f"  print_instalment({order_id}, {parts})")
        try:
            print("    prints:", instalment(order_id, parts))
            print(f"    prints: Order {order_id}: done")
            continue
        except LookupError as exc:
            name = str(exc)
        code, sqlcode = ERRORS[name]
        print(f"    {name} ({code}) raised in the inner block")
        for level in ("inner block", "print_instalment", "caller"):
            hit = catcher(level, handlers, name)
            if hit is None:
                print(f"    {level}: no handler names it")
                continue
            print(f"    {level}: WHEN {hit} runs, SQLCODE {sqlcode}")
            if level == "inner block":
                print(f"    prints: Order {order_id}: done")
            elif level == "print_instalment":
                print(f"    skipped: Order {order_id}: done")
                print("    the call returns normally")
            else:
                print("    skipped: the rest of the caller")
                return True
            break
        else:
            print(f"    client: reports {code} with ORA-06512 lines")
            return False
    print("  the caller ran every call")
    return True


run_caller("First caller block:", FIRST_CALLER, HANDLERS)
no_caller_handler = dict(HANDLERS, caller=[])
reached_end = run_caller("Second caller block (no handler):", SECOND_CALLER,
                         no_caller_handler)

First caller block:
  print_instalment(3, 3)
    prints: Order 3: 3 parts of 300.00
    prints: Order 3: done
  print_instalment(3, 0)
    ZERO_DIVIDE (ORA-01476) raised in the inner block
    inner block: no handler names it
    print_instalment: WHEN ZERO_DIVIDE runs, SQLCODE -1476
    skipped: Order 3: done
    the call returns normally
  print_instalment(7, 2)
    NO_DATA_FOUND (ORA-01403) raised in the inner block
    inner block: no handler names it
    print_instalment: no handler names it
    caller: WHEN NO_DATA_FOUND runs, SQLCODE 100
    skipped: the rest of the caller
Second caller block (no handler):
  print_instalment(7, 2)
    NO_DATA_FOUND (ORA-01403) raised in the inner block
    inner block: no handler names it
    print_instalment: no handler names it
    caller: no handler names it
    client: reports ORA-01403 with ORA-06512 lines