Exceptions and recovery

Sumatra’s error philosophy is simple: a wrong value is worse than a loud stop. Overflow, division by zero, and unexpected engine errors are faults that end the run — never silently absorbed, never replaced with a NULL. What the language gives you is precise control over how the run ends, and a commit model that makes recovery a re-run rather than a forensic exercise.

The fault model

Faults arrive in three lanes, and the distinction matters:

Lane	Examples	Catchable?
Typed faults from your compute	`ZERO_DIVIDE`, `OVERFLOW`, `NO_DATA_FOUND`	Yes, by name
Engine errors from SQL statements	constraint-style errors, MERGE cardinality, SQL runtime errors	Yes, as `OTHERS`
Infrastructure failures	network, authentication, engine unavailable	No — by design

Infrastructure failures are deliberately uncatchable: a WHEN OTHERS handler can never mask an outage as a handled condition. An infra failure always exits 1, so your scheduler’s alert lane stays honest. The typed faults:

ZERO_DIVIDE — division by zero in compute.
OVERFLOW — integer width overflow on + - * or a cast, decimal precision overflow, a float operation that would produce NaN or Infinity, or an out-of-range date result.
NO_DATA_FOUND — a keyed-collection probe missed (or used a NULL key), or a SELECT ... INTO found no row.

There are no user-defined exception names in this release, and an empty read is not a fault — a read that matches zero rows is a clean no-op.

The EXCEPTION block

A program may end with one handler block:

BEGIN
  ...
EXCEPTION
  WHEN ZERO_DIVIDE THEN STOP;    -- anticipated: stop cleanly, keep the prefix
  WHEN OVERFLOW    THEN STOP;
  WHEN OTHERS      THEN RAISE;   -- anything else: surface it loudly
END;

The rules:

Handler actions are exactly two: STOP or RAISE. There is no handler that patches a value and continues — a handler never resumes the loop it interrupted.
WHEN OTHERS must be the last handler, and no exception may be handled twice.
One EXCEPTION block per program, covering the whole body.

STOP versus RAISE

STOP is the graceful ending for a fault you anticipated: the in-flight batch’s open transaction rolls back, every previously committed batch stays durable, and the process exits 2 with Stopped — STOP handled; committed prefix retained. A stop is not a success — exit 2 exists precisely so schedulers can tell “finished” from “stopped early with valid partial progress”. RAISE rethrows: the run dies with exit 1, for faults that should page a human. An unhandled fault behaves the same way.

Raising faults yourself

Business invariants can reuse the typed faults, with an optional condition:

RAISE ZERO_DIVIDE WHEN rec.denominator = 0;   -- conditional raise

IF rec.qty > 1000000 THEN
  RAISE OVERFLOW;                             -- unconditional
END IF;

ZERO_DIVIDE and OVERFLOW can be raised explicitly; NO_DATA_FOUND and OTHERS cannot.

The durable prefix

The commit grain you chose in the program (see COMMIT: placement is meaning) decides what a fault leaves behind:

Per-batch commits — a fault on batch 40 leaves batches 1–39 committed. That committed prefix is always valid data, because a batch only commits after its writes applied.
At-end commit — a fault anywhere leaves nothing committed; all-or-nothing.

The standard recovery pattern makes the program re-runnable: filter the read on a done-flag and flip the flag as part of each row’s write.

BEGIN
  FOR batch IN (SELECT account_id, gross, factor, weight, risk_score, scored
                FROM risk_inputs
                WHERE scored = FALSE)          -- only unprocessed rows
      ORDER KEY (account_id)
      BATCH SIZE 10000
  LOOP
    FOR rec IN batch LOOP
      rec.risk_score := rec.gross * rec.factor / rec.weight;
      rec.scored     := TRUE;                  -- flag rides the same write
      UPDATE risk_inputs SET ROW = rec WHERE account_id = rec.account_id;
    END LOOP;
    COMMIT;
  END LOOP;
EXCEPTION
  WHEN ZERO_DIVIDE THEN STOP;                  -- bad row stops the run at exit 2
END;

If a zero weight stops this run, every earlier batch is already committed and flagged. Fix the data, run the same program again, and the read picks up exactly the unprocessed remainder.

Re-running is always your action — Sumatra has no automatic retry or resume. And note that carried variables restart from their DECLARE initializers on a re-run: design carried state so it is either derivable from the data (as in a per-customer reset) or irrelevant across runs.

Guard, don’t catch

A handler stops the run; it cannot skip the faulting row and carry on. When bad rows are expected and should be tolerated, guard before the fault instead:

FOR rec IN batch LOOP
  CONTINUE WHEN rec.weight = 0;      -- skip the rows that would fault
  rec.risk_score := rec.gross * rec.factor / rec.weight;
  ...
END LOOP;

Use guards for row-level tolerance, and the EXCEPTION block for stopping the run cleanly when something genuinely unexpected happens.

Exit codes for schedulers

Exit	Meaning	Typical scheduler action
`0`	Completed (including a clean `RETURN;`)	Nothing
`2`	Stopped by a handler; committed prefix retained	Re-run after investigating the data
`1`	Unhandled fault, `RAISE`, compile error, or infrastructure failure	Alert a human

Introduction

Getting Started

Writing Programs

Reference

The fault model

The EXCEPTION block

STOP versus RAISE

Raising faults yourself

The durable prefix

Guard, don’t catch

Exit codes for schedulers

​The fault model

​The EXCEPTION block

​STOP versus RAISE

​Raising faults yourself

​The durable prefix

​Guard, don’t catch

​Exit codes for schedulers

The fault model

The EXCEPTION block

STOP versus RAISE

Raising faults yourself

The durable prefix

Guard, don’t catch

Exit codes for schedulers