5.15. EXCEPTION

This section describes how to create, modify and delete custom exceptions for use in error handlers in PSQL modules.

5.15.1. CREATE EXCEPTION

Used forCreating a new exception for use in PSQL modules

Available inDSQL, ESQL

Syntax 

  |CREATE EXCEPTION exception_name '<message>'
  | 
  |<message> ::= <message-part> [<message-part> ...]
  | 
  |<message-part> ::=
  |    <text>
  |  | @<slot>
  | 
  |<slot> ::= one of 1..9

Table 5.42. CREATE EXCEPTION Statement Parameters
ParameterDescription

exception_name

Exception name. The maximum length is 31 characters

message

Default error message. The maximum length is 1,021 characters

text

Text of any character

slot

Slot number of a parameter. Numbering starts at 1. Maximum slot number is 9.

The statement CREATE EXCEPTION creates a new exception for use in PSQL modules. If an exception with the same name exists, the statement will fail with an appropriate error message.

The exception name is a standard identifier. In a Dialect 3 database, it can be enclosed in double quotes to make it case-sensitive and, if required, to use characters that are not valid in regular identifiers. See Identifiers for more information.

The default message is stored in character set NONE, i.e. in characters of any single-byte character set. The text can be overridden in the PSQL code when the exception is thrown.

The error message may contain parameter slots that can be filled when raising the exception.

🛑
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.

ⓘ
Note

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

5.15.1.1. Who Can Create an Exception

The CREATE EXCEPTION statement can be executed by:

The user executing the CREATE EXCEPTION statement becomes the owner of the exception.

5.15.1.2. CREATE EXCEPTION Examples

Creating an exception named E_LARGE_VALUE 

  |CREATE EXCEPTION E_LARGE_VALUE
  |  'The value is out of range';

Creating a parameterized exception E_INVALID_VALUE 

  |CREATE EXCEPTION E_INVALID_VALUE
  |  'Invalid value @1 for field @2';

☞
Tips

Grouping CREATE EXCEPTION statements together in system update scripts will simplify working with them and documenting them. A system of prefixes for naming and categorising groups of exceptions is recommended.

See alsoSection 5.15.2, “ALTER EXCEPTION”, Section 5.15.3, “CREATE OR ALTER EXCEPTION”, Section 5.15.4, “DROP EXCEPTION”, Section 5.15.5, “RECREATE EXCEPTION”

5.15.2. ALTER EXCEPTION

Used forModifying the message returned from a custom exception

Available inDSQL, ESQL

Syntax 

  |ALTER EXCEPTION exception_name '<message>'
  | 
  |!! See syntax of CREATE EXCEPTION for further rules !!

The statement ALTER EXCEPTION can be used at any time, to modify the default text of the message.

5.15.2.1. Who Can Alter an Exception

The ALTER EXCEPTION statement can be executed by:

  • Administrators

  • The owner of the exception

  • Users with the ALTER ANY EXCEPTION privilege

5.15.2.2. ALTER EXCEPTION Examples

Changing the default message for the exception E_LARGE_VALUE 

  |ALTER EXCEPTION E_LARGE_VALUE
  |  'The value exceeds the prescribed limit of 32,765 bytes';

See alsoSection 5.15.1, “CREATE EXCEPTION”, Section 5.15.3, “CREATE OR ALTER EXCEPTION”, Section 5.15.4, “DROP EXCEPTION”, Section 5.15.5, “RECREATE EXCEPTION”

5.15.3. CREATE OR ALTER EXCEPTION

Used forModifying the message returned from a custom exception, if the exception exists; otherwise, creating a new exception

Available inDSQL

Syntax 

  |CREATE OR ALTER EXCEPTION exception_name '<message>'
  | 
  |!! See syntax of CREATE EXCEPTION for further rules !!

The statement CREATE OR ALTER EXCEPTION is used to create the specified exception if it does not exist, or to modify the text of the error message returned from it if it exists already. If an existing exception is altered by this statement, any existing dependencies will remain intact.

5.15.3.1. CREATE OR ALTER EXCEPTION Example

Changing the message for the exception E_LARGE_VALUE 

  |CREATE OR ALTER EXCEPTION E_LARGE_VALUE
  |  'The value is higher than the permitted range 0 to 32,765';

See alsoSection 5.15.1, “CREATE EXCEPTION”, Section 5.15.2, “ALTER EXCEPTION”, Section 5.15.5, “RECREATE EXCEPTION”

5.15.4. DROP EXCEPTION

Used forDeleting a custom exception

Available inDSQL, ESQL

Syntax 

  |DROP EXCEPTION exception_name

Table 5.43. DROP EXCEPTION Statement Parameter
ParameterDescription

exception_name

Exception name

The statement DROP EXCEPTION is used to delete an exception. Any dependencies on the exception will cause the statement to fail, and the exception will not be deleted.

5.15.4.1. Who Can Drop an Exception

The DROP EXCEPTION statement can be executed by:

  • Administrators

  • The owner of the exception

  • Users with the DROP ANY EXCEPTION privilege

5.15.4.2. DROP EXCEPTION Examples

Dropping exception E_LARGE_VALUE 

  |DROP EXCEPTION E_LARGE_VALUE;

See alsoSection 5.15.1, “CREATE EXCEPTION”, Section 5.15.5, “RECREATE EXCEPTION”

5.15.5. RECREATE EXCEPTION

Used forCreating a new custom exception or recreating an existing one

Available inDSQL

Syntax 

  |RECREATE EXCEPTION exception_name '<message>'
  | 
  |!! See syntax of CREATE EXCEPTION for further rules !!

The statement RECREATE EXCEPTION creates a new exception for use in PSQL modules. If an exception with the same name exists already, the RECREATE EXCEPTION statement will try to drop it and create a new one. If there are any dependencies on the existing exception, the attempted deletion fails and RECREATE EXCEPTION is not executed.

5.15.5.1. RECREATE EXCEPTION Example

Recreating the E_LARGE_VALUE exception 

  |RECREATE EXCEPTION E_LARGE_VALUE
  |  'The value exceeds its limit';

See alsoSection 5.15.1, “CREATE EXCEPTION”, Section 5.15.4, “DROP EXCEPTION”, Section 5.15.3, “CREATE OR ALTER EXCEPTION”