Firebird 2.5 Language ReferenceFirebird 2.5 Language ReferenceData Definition (DDL) Statements → EXCEPTION
Firebird Firebird Prev: SEQUENCE (GENERATOR)Firebird 2.5 Language ReferenceUp: Data Definition (DDL) StatementsNext: COLLATION

EXCEPTION

Table of Contents

CREATE EXCEPTION
ALTER EXCEPTION
CREATE OR ALTER EXCEPTION
DROP EXCEPTION
RECREATE EXCEPTION

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

CREATE EXCEPTION

Used for:  Creating a new exception for use in PSQL modules

Available in: DSQL, ESQL

Syntax: 

CREATE EXCEPTION exception_name 'message'
        

Table 5.36. CREATE EXCEPTION Statement Parameters

Parameter Description
exception_name Exception name. The maximum length is 31 characters
message Default error message. The maximum length is 1,021 characters


The statement CREATE EXCEPTION creates a new exception for use in PSQL modules. If an exception of 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.

Any user connected to the database can create an exception.

Examples: 

  1. Creating an exception named E_LARGE_VALUE:
    CREATE EXCEPTION E_LARGE_VALUE
           'The value is out of range';
                
  2. Creating an exception named ERROR_REFIN_RATE:
    CREATE EXCEPTION ERROR_REFIN_RATE
      'Error detected in the spread of discount rates';
                
[Tip] 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.

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

See also:  ALTER EXCEPTION, CREATE OR ALTER EXCEPTION, DROP EXCEPTION, RECREATE EXCEPTION

ALTER EXCEPTION

Used for:  Modifying a the message returned from a custom exception

Available in: DSQL, ESQL

Syntax: 

ALTER EXCEPTION exception_name 'message'
        

Table 5.37. ALTER EXCEPTION Statement Parameters

Parameter Description
exception_name Exception name
message New default error message. The maximum length is 1,021 characters


The statement ALTER EXCEPTION can be used at any time, to modify the default text of the message. Any user connected to the database can alter an exception message.

Examples: 

  1. 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';
                
  2. Changing the default message for the exception ERROR_REFIN_RATE:
    ALTER EXCEPTION ERROR_REFIN_RATE 'Rate is outside the allowed range';
                

See also:  CREATE EXCEPTION, CREATE OR ALTER EXCEPTION, DROP EXCEPTION, RECREATE EXCEPTION

CREATE OR ALTER EXCEPTION

Used for:  Modifying a the message returned from a custom exception, if the exception exists; otherwise, creating a new exception

Available in: DSQL

Syntax: 

CREATE OR ALTER EXCEPTION exception_name 'message'
        

Table 5.38. CREATE OR ALTER EXCEPTION Statement Parameters

Parameter Description
exception_name Exception name
message Error message. The maximum length is limited to 1,021 characters


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.

Any user connected to the database can use this statement to create an exception or alter the text of one that already exists.

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 also:  CREATE EXCEPTION, ALTER EXCEPTION, RECREATE EXCEPTION

DROP EXCEPTION

Used for:  Deleting a custom exception

Available in: DSQL, ESQL

Syntax: 

DROP EXCEPTION exception_name
        

Table 5.39. DROP EXCEPTION Statement Parameter

Parameter Description
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.

If an exception is used only in stored procedures, it can be deleted at any time. If it is used in a trigger, it cannot be deleted.

In planning to delete an exception, all references to it should first be removed from the code of stored procedures, to avoid its absence causing errors.

Any user connected to the database can delete an exception.

Examples: 

  1. Deleting exception ERROR_REFIN_RATE:
    DROP EXCEPTION ERROR_REFIN_RATE;
                
  2. Deleting exception E_LARGE_VALUE:
    DELETE EXCEPTION E_LARGE_VALUE;
                

See also:  CREATE EXCEPTION, RECREATE EXCEPTION

RECREATE EXCEPTION

Used for:  Creating a new custom exception or recreating an existing one

Available in: DSQL

Syntax: 

RECREATE EXCEPTION exception_name 'message'
        

Table 5.40. RECREATE EXCEPTION Statement Parameters

Parameter Description
exception_name Exception name. The maximum length is 31 characters
message Error message. The maximum length is limited to 1,021 characters


The statement RECREATE EXCEPTION creates a new exception for use in PSQL modules. If an exception of the same name exists already, the RECREATE EXCEPTION statement will try to delete 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.

Any user connected to the database can [re]create an exception.

Example:  Recreating the E_LARGE_VALUE exception:

RECREATE EXCEPTION E_LARGE_VALUE 
       'The value exceeds its limit';
            

See also:  CREATE EXCEPTION, DROP EXCEPTION, CREATE OR ALTER EXCEPTION

Prev: SEQUENCE (GENERATOR)Firebird 2.5 Language ReferenceUp: Data Definition (DDL) StatementsNext: COLLATION
Firebird 2.5 Language ReferenceFirebird 2.5 Language ReferenceData Definition (DDL) Statements → EXCEPTION