Exception handling v16

By default, any error occurring in an SPL program stops the program from executing. You can trap errors and recover from them by using a BEGIN block with an EXCEPTION section.


The syntax is an extension of the normal syntax for a BEGIN block:

    <declarations> ]
    WHEN <condition> [ OR <condition> ]... THEN
  [ WHEN <condition> [ OR <condition> ]... THEN
      <handler_statements> ]...

Error handling process

  • If no error occurs, this form of block executes all the statements, and then control passes to the next statement after END.
  • If an error occurs in the statements, further processing of the statements is abandoned, and control passes to the EXCEPTION list. The list is searched for the first condition matching the error.
  • If a match is found, the corresponding handler_statements are executed, and then control passes to the next statement after END.
  • If no match is found, the error propagates out as though the EXCEPTION clause wasn't there. You can catch the error by an enclosing block with EXCEPTION. If there is no enclosing block, it aborts the subprogram.

The special condition named OTHERS matches every error type. Condition names aren't case sensitive.

If a new error occurs in the selected handler_statements, this EXCEPTION clause can't catch it, but it is propagated out. A surrounding EXCEPTION clause might catch it.

Performance implications

Heavy use of the EXCEPTION clause can have visible performance consequences. An EXCEPTION clause establishes a sub-transaction internally in the database server. If the code in the EXCEPTION clause completes without an error, the implicit sub-transaction commits. If an error occurs, it rolls back.

This process has some overhead. If the code protected by the EXCEPTION clause modifies the database, the transaction acquires another transaction ID (XID), increasing the overhead considerably. XID consumption is one factor determining how frequently each table in the database must be subject to VACUUM, so a very high rate of XID consumption leads to more VACUUM activity. Also, when any individual session has more than 64 XIDs assigned simultaneously, some tuple visibility checks incur more overhead.

List of condition names to use

Condition nameDescription
CASE_NOT_FOUNDThe application encountered a situation where none of the cases in CASE statement evaluates to TRUE and there is no ELSE condition.
COLLECTION_IS_NULLThe application attempted to invoke a collection method on a null collection, such as an uninitialized nested table.
CURSOR_ALREADY_OPENThe application attempted to open a cursor that's already open.
DUP_VAL_ON_INDEXThe application attempted to store a duplicate value that currently exists in a constrained column.
INVALID_CURSORThe application attempted to access an unopened cursor.
INVALID_NUMBERThe application encountered a data exception equivalent to SQLSTATE class code 22. INVALID_NUMBER is an alias for VALUE_ERROR.
NO_DATA_FOUNDNo rows satisfy the selection criteria.
OTHERSThe application encountered an exception that wasn't caught by a prior condition in the exception section.
SUBSCRIPT_BEYOND_COUNTThe application attempted to reference a subscript of a nested table or varray beyond its initialized or extended size.
SUBSCRIPT_OUTSIDE_LIMITThe application attempted to reference a subscript or extend a varray beyond its maximum size limit.
TOO_MANY_ROWSThe application encountered more than one row that satisfies the selection criteria where only one row is allowed to be returned.
VALUE_ERRORThe application encountered a data exception equivalent to SQLSTATE class code 22. VALUE_ERROR is an alias for INVALID_NUMBER.
ZERO_DIVIDEThe application tried to divide by zero.
User-defined ExceptionSee User-defined exceptions.

Condition names INVALID_NUMBER and VALUE_ERROR aren't compatible with Oracle databases for which these condition names are used for exceptions that result only from a failed conversion of a string to a numeric literal. In addition, for Oracle databases, an INVALID_NUMBER exception applies only to SQL statements, while a VALUE_ERROR exception applies only to procedural statements.