EXCEPTION

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:

Creating an exception named E_LARGE_VALUE:

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

Creating an exception named ERROR_REFIN_RATE:

CREATE EXCEPTION ERROR_REFIN_RATE
  'Error detected in the spread of discount rates';

	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.

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.

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:

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';

Changing the default message for the exception ERROR_REFIN_RATE:

ALTER EXCEPTION ERROR_REFIN_RATE 'Rate is outside the allowed range';

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';

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:

Deleting exception ERROR_REFIN_RATE:

DROP EXCEPTION ERROR_REFIN_RATE;

Deleting exception E_LARGE_VALUE:

DELETE EXCEPTION E_LARGE_VALUE;

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';