Exception Handling

Exception handling comes in handy when you have a query processing a lot of data: if there's a problem with a single row, it can be handled without interrupting the entire query. All errors have defined SQL states, error codes, and other identifiers, so you can check the log and see what errors have occurred (if any).

If the EXCEPTION clause is used within a compound statement, any processing exception emitted from statements will be caught with the flow of execution transferring to EXCEPTION statements. Any block level transaction started by this block will commit if the exception handler successfully completes. If another exception or the original exception is emitted from the exception handler the transaction will rollback.

There are two important things to keep in mind:

Any temporary tables or variables specific to the BLOCK will not be available to the exception handler statements;
Only processing exceptions which are typically caused by errors originating at the sources or with function execution are caught. A low-level internal error or Java RuntimeException will not be caught.

To aid in the processing of a caught exception the EXCEPTION clause specifies a group name that exposes the significant fields of the exception. The exception group will contain the following:

Variable	Type	Description
`STATE`	string	SQL State
`ERRORCODE`	integer	Error or vendor code. In the case of an internal exception, this will be the integer suffix of the `TEIIDxxxx` code
`TEIIDCODE`	string	Full event code. Typically `TEIIDxxxx`.
`EXCEPTION`	object	Exception being caught.
`CHAIN`	object	Chained exception or cause of the current exception

Only processed exceptions which are typically caused by errors originating at the sources or with function execution are caught. A low-level internal error or Java RuntimeException will not be caught.

The exception group name may not be the same as any higher level exception group or loop cursor name.

Example of Exception Group Handling

SQL

BEGIN
    DECLARE EXCEPTION e = SQLEXCEPTION 'this is bad' SQLSTATE 'xxxxx';
    RAISE variables.e;
EXCEPTION e
    IF (e.state = 'xxxxx')
        -- in this trivial example, we'll always hit this branch and just log the exception
        RAISE SQLWARNING e.exception;
    ELSE
        RAISE e.exception;
END