Custom Exceptions

Data Manipulation (DML) StatementsBuilt-in Scalar Functions

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

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

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

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

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

Custom Exceptions