This section describes how to create, modify and delete custom exceptions for use in error handlers in PSQL modules.
- languageFrançais
EXCEPTION
CREATE EXCEPTION
Creates a custom exception for use in PSQL modules
Available in
DSQL, ESQL
Syntax
CREATE EXCEPTION exception_name '<message>'
<message> ::= <message-part> [<message-part> ...]
<message-part> ::=
<text>
| @<slot>
<slot> ::= one of 1..9
| Parameter | Description |
|---|---|
exception_name |
Exception name. The maximum length is 63 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 raise an error.
The exception name is an identifier, 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 |
|
Note
|
Custom exceptions are stored in the system table |
Who Can Create an Exception
The CREATE EXCEPTION statement can be executed by:
-
Users with the
CREATE EXCEPTIONprivilege
The user executing the CREATE EXCEPTION statement becomes the owner of the exception.
CREATE EXCEPTION Examples
Creating an exception named
E_LARGE_VALUECREATE EXCEPTION E_LARGE_VALUE
'The value is out of range';Creating a parameterized exception
E_INVALID_VALUECREATE EXCEPTION E_INVALID_VALUE
'Invalid value @1 for field @2';ALTER EXCEPTION
Alters the default message of a custom exception
Available in
DSQL, ESQL
Syntax
ALTER EXCEPTION exception_name '<message>'
!! See syntax of CREATE EXCEPTION for further rules !!
Who Can Alter an Exception
The ALTER EXCEPTION statement can be executed by:
-
The owner of the exception
-
Users with the
ALTER ANY EXCEPTIONprivilege
ALTER EXCEPTION Examples
Changing the default message for the exception
E_LARGE_VALUEALTER EXCEPTION E_LARGE_VALUE
'The value exceeds the prescribed limit of 32,765 bytes';CREATE OR ALTER EXCEPTION
Creates a custom exception if it doesn’t exist, or alters a custom exception
Available in
DSQL
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.
CREATE OR ALTER EXCEPTION Example
Changing the message for the exception
E_LARGE_VALUECREATE OR ALTER EXCEPTION E_LARGE_VALUE
'The value is higher than the permitted range 0 to 32,765';DROP EXCEPTION
Drops a custom exception
Available in
DSQL, ESQL
Syntax
DROP EXCEPTION exception_name
| 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.
Who Can Drop an Exception
The DROP EXCEPTION statement can be executed by:
-
The owner of the exception
-
Users with the
DROP ANY EXCEPTIONprivilege
DROP EXCEPTION Examples
Dropping exception
E_LARGE_VALUEDROP EXCEPTION E_LARGE_VALUE;RECREATE EXCEPTION
Drops a custom exception if it exists, and creates a custom exception
Available in
DSQL
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.
RECREATE EXCEPTION Example
Recreating the
E_LARGE_VALUE exceptionRECREATE EXCEPTION E_LARGE_VALUE
'The value exceeds its limit';