FirebirdSQL logo

Custom Exceptions

Custom exceptions can be declared in the database as persistent objects and called in PSQL code to signal specific errors;for example, to enforce certain business rules.A custom exception consists of an identifier, and a default message of 1021 bytes.For details, see CREATE EXCEPTION.

EXCEPTION

Throws a user-defined exception or rethrows an exception

Syntax
EXCEPTION [
    exception_name
    [ custom_message
    | USING (<value_list>)]
  ]

<value_list> ::= <val> [, <val> ...]
Table 1. EXCEPTION Statement Parameters
Argument Description

exception_name

Exception name

custom_message

Alternative message text to be returned to the caller interface when an exception is thrown.Maximum length of the text message is 1,021 bytes

val

Value expression that replaces parameter slots in the exception message text

The EXCEPTION statement with exception_name throws the user-defined exception with the specified name.An alternative message text of up to 1,021 bytes can optionally override the exception’s default message text.

The default exception message can contain slots for parameters that can be filled when throwing an exception.To pass parameter values to an exception, use the USING clause.Considering, in left-to-right order, each parameter passed in the exception-raising statement as “the Nth”, with N starting at 1:

  • If the Nth parameter is not passed, its slot is not replaced

  • If a NULL parameter is passed, the slot will be replaced with the string “*** null ***

  • If more parameters are passed than are defined in the exception message, the surplus ones are ignored

  • The maximum number of parameters is 9

  • The maximum message length, including parameter values, is 1053 bytes

Note

The status vector is generated this code combination isc_except, <exception number>, isc_formatted_exception, <formatted exception message>, <exception parameters>.

The error code used (isc_formatted_exception) was introduced in Firebird 3.0, so the client must be at least version 3.0, or at least use the firebird.msg from version 3.0 or higher, to translate the status vector to a string.

Warning

If the message contains a parameter slot number that is greater than 9, the second and subsequent digits will be treated as literal text.For example @10 will be interpreted as slot 1 followed by a literal ‘0’.

As an example:

CREATE EXCEPTION ex1
  'something wrong in @1@2@3@4@5@6@7@8@9@10@11';
SET TERM ^;
EXECUTE BLOCK AS
BEGIN
  EXCEPTION ex1 USING ('a', 'b', 'c', 'd', 'e', 'f', 'g', 'h', 'i');
END^

This will produce the following output

Statement failed, SQLSTATE = HY000
exception 1
-EX1
-something wrong in abcdefghia0a1

Exceptions can be handled in a [fblangref50-psql-when] statement.If an exception is not handled in a module, then the effects of the actions executed inside this module are cancelled, and the caller program receives the exception (either the default text, or the custom text).

Within the exception-handling block — and only within it — the caught exception can be re-thrown by executing the EXCEPTION statement without parameters.If located outside the block, the re-thrown EXCEPTION call has no effect.

Custom exceptions are stored in the system table RDB$EXCEPTIONS.

EXCEPTION Examples

  1. Throwing an exception upon a condition in the SHIP_ORDER stored procedure:

    CREATE OR ALTER PROCEDURE SHIP_ORDER (
      PO_NUM CHAR(8))
    AS
      DECLARE VARIABLE ord_stat  CHAR(7);
      DECLARE VARIABLE hold_stat CHAR(1);
      DECLARE VARIABLE cust_no   INTEGER;
      DECLARE VARIABLE any_po    CHAR(8);
    BEGIN
      SELECT
        s.order_status,
        c.on_hold,
        c.cust_no
      FROM
        sales s, customer c
      WHERE
        po_number = :po_num AND
        s.cust_no = c.cust_no
      INTO :ord_stat,
           :hold_stat,
           :cust_no;
    
      IF (ord_stat = 'shipped') THEN
        EXCEPTION order_already_shipped;
      /* Other statements */
    END
  2. Throwing an exception upon a condition and replacing the original message with an alternative message:

    CREATE OR ALTER PROCEDURE SHIP_ORDER (
      PO_NUM CHAR(8))
    AS
      DECLARE VARIABLE ord_stat  CHAR(7);
      DECLARE VARIABLE hold_stat CHAR(1);
      DECLARE VARIABLE cust_no   INTEGER;
      DECLARE VARIABLE any_po    CHAR(8);
    BEGIN
      SELECT
        s.order_status,
        c.on_hold,
        c.cust_no
      FROM
        sales s, customer c
      WHERE
        po_number = :po_num AND
        s.cust_no = c.cust_no
      INTO :ord_stat,
           :hold_stat,
           :cust_no;
    
      IF (ord_stat = 'shipped') THEN
        EXCEPTION order_already_shipped
          'Order status is "' || ord_stat || '"';
      /* Other statements */
    END
  3. Using a parameterized exception:

    CREATE EXCEPTION EX_BAD_SP_NAME
      'Name of procedures must start with' '@ 1' ':' '@ 2' '' ;
    ...
    CREATE TRIGGER TRG_SP_CREATE BEFORE CREATE PROCEDURE
    AS
      DECLARE SP_NAME VARCHAR(255);
    BEGIN
      SP_NAME = RDB$GET_CONTEXT ('DDL_TRIGGER' , 'OBJECT_NAME');
      IF (SP_NAME NOT STARTING 'SP_') THEN
        EXCEPTION EX_BAD_SP_NAME USING ('SP_', SP_NAME);
    END
  4. Logging an error and re-throwing it in the WHEN block:

    CREATE PROCEDURE ADD_COUNTRY (
      ACountryName COUNTRYNAME,
      ACurrency VARCHAR(10))
    AS
    BEGIN
      INSERT INTO country (country,
                           currency)
      VALUES (:ACountryName,
              :ACurrency);
      WHEN ANY DO
      BEGIN
        -- write an error in log
        IN AUTONOMOUS TRANSACTION DO
          INSERT INTO ERROR_LOG (PSQL_MODULE,
                                 GDS_CODE,
                                 SQL_CODE,
                                 SQL_STATE)
          VALUES ('ADD_COUNTRY',
                  GDSCODE,
                  SQLCODE,
                  SQLSTATE);
        -- Re-throw exception
        EXCEPTION;
      END
    END