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 roll back.
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 |
The exception group name may not be the same as any higher-level exception group or loop cursor name.
Example of Exception Group Handling
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