Used forCreating a new exception for use in PSQL modules

Available inDSQL, 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.

See alsoSection 5.12.2, “ALTER EXCEPTION”, Section 5.12.3, “CREATE OR ALTER EXCEPTION”, Section 5.12.4, “DROP EXCEPTION”, Section 5.12.5, “RECREATE EXCEPTION”

Used forModifying the message returned from a custom exception

Available inDSQL, 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.

See alsoSection 5.12.1, “CREATE EXCEPTION”, Section 5.12.3, “CREATE OR ALTER EXCEPTION”, Section 5.12.4, “DROP EXCEPTION”, Section 5.12.5, “RECREATE 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'

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.

ExampleChanging 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.12.1, “CREATE EXCEPTION”, Section 5.12.2, “ALTER EXCEPTION”, Section 5.12.5, “RECREATE EXCEPTION”

Used forDeleting a custom exception

Available inDSQL, 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.

See alsoSection 5.12.1, “CREATE EXCEPTION”, Section 5.12.5, “RECREATE EXCEPTION”

Used forCreating a new custom exception or recreating an existing one

Available inDSQL

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.

ExampleRecreating the E_LARGE_VALUE exception:

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

See alsoSection 5.12.1, “CREATE EXCEPTION”, Section 5.12.4, “DROP EXCEPTION”, Section 5.12.3, “CREATE OR ALTER EXCEPTION”