ON EXCEPTION

Use the ON EXCEPTION statement to specify actions to be taken for any error, or for a list of one or more specified errors, during execution of a statement block.

Syntax

Notes:

¹ See Statement Block


Element	Description	Restrictions	Syntax
error_data_var	SPL variable to receive a string returned by an SQL error or by a user-defined exception	Must be a character type to receive the error information. Must be valid in current statement block.	Identifier
error_number	SQL error number or a number defined by a RAISE EXCEPTION statement that is to be trapped	Must be of integer type. Must be valid in current statement block.	Literal Number
ISAM_error_var	SPL variable that receives the ISAM error number of the exception raised	Same as for error_number	Identifier
SQL_error_var	SPL variable that receives the SQL error number of the exception raised	Same as for ISAM_error_var	Identifier

Usage

The ON EXCEPTION statement, together with the RAISE EXCEPTION statement, provides an error-trapping and error-recovery mechanism for SPL routines. ON EXCEPTION can specify the errors that you want to trap as the SPL routine executes, and specifies the action to take if the error occurs within the statement block. The ON EXCEPTION statement can list one or more specific error numbers in the IN clause, or it can trap all errors (or any error) if the IN clause is omitted.

A statement block can include more than one ON EXCEPTION statement. The exceptions that are trapped can be either system-defined or user-defined.

The scope of the ON EXCEPTION statement is the statement block that contains it, and any statement blocks that are nested within that statement block, unless one of the nested statement blocks provides an ON EXCEPTION statement that overrides the outer one.

When an exception is trapped, the error status is cleared.

If you specify a variable to receive an ISAM error, but no accompanying ISAM error exists, a zero (0) is assigned to the variable. If you specify a variable to receive the error text, but none exists, the variable stores an empty string.

No ON EXCEPTION Support in Triggered Actions

The ON EXCEPTION statement has no effect when it is issued from an SPL routine in the following calling contexts:

in a trigger routine,
in the Action clause or the Correlated Action clause of a trigger on a table,
in the Action clause of an INSTEAD OF trigger on a view.

When a UDR includes ON EXCEPTION in any of these contexts, the database server ignores the ON EXCEPTION statement.