Skip to main content
Skip table of contents

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

STATEstringSQL State
ERRORCODEintegerError or vendor code. In the case of an internal exception, this will be the integer suffix of the TEIIDxxxx code
TEIIDCODEstringFull event code. Typically TEIIDxxxx.
EXCEPTIONobjectException being caught.
CHAINobjectChained 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
JavaScript errors detected

Please note, these errors can depend on your browser setup.

If this problem persists, please contact our support.