5.12. EXCEPTION

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

5.12.1. CREATE EXCEPTION

Used for

Creating a new exception for use in PSQL modules

Available in

DSQL, ESQL

Syntax

CREATE EXCEPTION exception_name 'message'

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

See also

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

5.12.2. ALTER EXCEPTION

Used for

Modifying the message returned from a custom exception

Available in

DSQL, ESQL

Syntax

ALTER EXCEPTION exception_name 'message'

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

5.12.3. CREATE OR ALTER EXCEPTION

Used for

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

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

5.12.4. DROP EXCEPTION

Used for

Deleting a custom exception

Available in

DSQL, ESQL

Syntax

DROP EXCEPTION 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:

   DROP EXCEPTION E_LARGE_VALUE;

See also

CREATE EXCEPTION, RECREATE EXCEPTION

5.12.5. RECREATE EXCEPTION

Used for

Creating a new custom exception or recreating an existing one

Available in

DSQL

Syntax

RECREATE EXCEPTION exception_name 'message'

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