Trap errors in SPL programs using the BEGIN EXCEPTION block - PolarDB

By default, any error in an SPL program aborts execution. To recover from errors, add an EXCEPTION section to a BEGIN block — this lets you catch specific errors and handle them without terminating the program.

Syntax

The EXCEPTION section extends the standard BEGIN block:

[ DECLARE
    declarations ]
  BEGIN
    statements
  EXCEPTION
    WHEN condition [ OR condition ]... THEN
      handler_statements
  [ WHEN condition [ OR condition ]... THEN
      handler_statements ]...
  END;

How it works

When an error occurs inside statements:

Execution of statements stops immediately.
The EXCEPTION list is searched top to bottom for the first condition that matches the error.
If a match is found, the corresponding handler_statements run, then control passes to the next statement after END.
If no match is found, the error propagates out of the block as if the EXCEPTION clause did not exist. An enclosing block with its own EXCEPTION clause can catch it. If no enclosing block exists, the error aborts the subprogram.

If a new error occurs inside handler_statements, the current EXCEPTION clause cannot catch it — only a surrounding EXCEPTION clause can.

Use OTHERS to catch any error not matched by an earlier condition. Condition names are case-insensitive. Where possible, match specific named conditions instead of relying solely on OTHERS — this keeps error handling precise and avoids masking unexpected errors.

Example

DECLARE
  stock_price  NUMBER := 9.73;
  net_earnings NUMBER := 0;
  pe_ratio     NUMBER;
BEGIN
  pe_ratio := stock_price / net_earnings;  -- raises ZERO_DIVIDE
EXCEPTION
  WHEN ZERO_DIVIDE THEN
    pe_ratio := NULL;  -- handle division by zero: set result to NULL
  WHEN OTHERS THEN
    NULL;  -- suppress unexpected errors silently
END;

Predefined condition names

Condition name	Description
`CASE_NOT_FOUND`	None of the cases in a `CASE` statement evaluates to TRUE and no ELSE condition exists.
`COLLECTION_IS_NULL`	A collection method was invoked on a null collection, such as an uninitialized nested table.
`CURSOR_ALREADY_OPEN`	An attempt was made to open a cursor that is already open.
`DUP_VAL_ON_INDEX`	An attempt was made to store a duplicate value that currently exists within a constrained column.
`INVALID_CURSOR`	An attempt was made to access an unopened cursor.
`INVALID_NUMBER`	A data exception occurred (equivalent to SQLSTATE class code 22). `INVALID_NUMBER` is an alias for `VALUE_ERROR`.
`NO_DATA_FOUND`	No rows satisfy the selection criteria.
`OTHERS`	The exception was not caught by any earlier condition in the exception section.
`SUBSCRIPT_BEYOND_COUNT`	An attempt was made to reference a subscript of a nested table or varray beyond its initialized or extended size.
`SUBSCRIPT_OUTSIDE_LIMIT`	An attempt was made to reference a subscript or extend a varray beyond its maximum size limit.
`TOO_MANY_ROWS`	More than one row satisfies the selection criteria where only one row is allowed.
`VALUE_ERROR`	A data exception occurred (equivalent to SQLSTATE class code 22). `VALUE_ERROR` is an alias for `INVALID_NUMBER`.
`ZERO_DIVIDE`	An attempt was made to divide by zero.
User-defined exception	For more information, see User-defined exceptions.

INVALID_NUMBER and VALUE_ERROR are not compatible with Oracle databases. In Oracle, these condition names apply only to exceptions from a failed conversion of a string to a numeric literal. In addition, in Oracle, INVALID_NUMBER applies only to SQL statements, while VALUE_ERROR applies only to procedural statements.