Firebird 2.0
PSQL Reference
Manual

EXCEPTION HANDLERS

Purpose

Use exception handlers to catch error conditions in a PSQL program or in a specific compound statement block.

Syntax

<excep_handlers> ::=
     <excep_handler>
   ! <excep_handler> <excep_handlers>

<excep_handler> ::=
   WHEN <error_list> DO <psql_statement>;

<error_list> ::=
     <error>
   ! <error> , <error_list>

<error> ::=
     <sqlcode_error>
   ! <gdscode_error>
   ! <user_error>
   ! ANY


Element
Description
psql_statement
A PSQL statement, typically a compound statement block

Semantics

Instead of terminating when an exception occurs, a PSQL program or compound statement block can respond to and perhaps correct the error condition by “handling” the exception, through a WHEN exception handler.

A WHEN exception handler is specific to one or more error conditions. Which error conditions the handler catches is defined by the error list following the WHEN keyword. Exceptions can be defined in one of four ways:

  • By refering to the SQL error code of a system exception

  • By refering to the name of a system exception

  • By refering to the name of a user exception

  • Or by specifying ANY to create a “catch all” exception handler

When an exception is raised within a block, Firebird:

  • Seeks a handler for the exception in the current block. If no such handler is found:

  • it undoes any actions performed in the block;

  • backs out one level to the enclosing block and seeks a handler there. Firebird continues backing out levels and undoing actions until a handler is found. If no suitable handler is found, the PSQL program is terminated and the exception is reported to the caller.

  • If a handler for the exception is found in the current or an enclosing block:

  • the error handling statement or block of statements specified in the handler are executed and

  • processing continues at the statement that follows the block with the exception handler. If the handler was in the outermost block (i.e. the PSQL program body), control is returned to the calling application.

An exception that has been handled does not report the exception to the calling application, i.e. it does not return an error message.

Examples

The below example shows some valid exception handling clauses:

DECLARE a INTEGER;
BEGIN
   a = 23 / a;
   WHEN SQLCODE -802 DO
      BEGIN
      a = 12;
      END
   WHEN GDSCODE arith_except DO
      BEGIN
      EXEPTION;
      END
   WHEN EXCEPTION my_error DO
      BEGIN
      EXEPTION my_error 'User defined error raised';
      END
   WHEN ANY DO
      BEGIN
      a = 12;
      EXEPTION;
      END
END;


See also

EXCEPTION statement


previous page goto index next page

Legal information