CREATE EXCEPTION

Go Up to Statement and Function Reference (Language Reference Guide)

Creates a used-defined error and associated message for use in stored procedures and triggers. Available in DSQL and isql.

CREATE EXCEPTION <name> '<message>';

Important:
In SQL statements passed to DSQL, omit the terminating semicolon. In isql, the semicolon is a terminating symbol for the statement, so it must be included.

Argument	Description
<name>	Name associated with the exception message; must be unique among exception names in the database.
‘<message>’	Quoted string containing alphanumeric characters and punctuation; maximum length = 78 characters.

Description: CREATE EXCEPTION creates an exception, a user-defined error with an associated message. Exceptions may be raised in triggers and stored procedures.

Exceptions are global to the database. The same message or set of messages is available to all stored procedures and triggers in an application. For example, a database can have English and French versions of the same exception messages and use the appropriate set as needed.

When raised by a trigger or a stored procedure, an exception:

Terminates the trigger or procedure in which it was raised and undoes any actions performed (directly or indirectly) by it.
Returns an error message to the calling application. In isql, the error message appears on the screen, unless output is redirected.

Exceptions may be trapped and handled with a WHEN statement in a stored procedure or trigger.

Examples: This isql statement creates the exception, UNKNOWN_EMP_ID:

CREATE EXCEPTION UNKNOWN_EMP_ID 'Invalid employee number or project id.';

The following statement from a stored procedure raises the previously-created exception when SQLCODE -530 is set, which is a violation of a FOREIGN KEY constraint:

. . .
WHEN SQLCODE -530 DO
EXCEPTION UNKNOWN_EMP_ID;
. . .

Advance To:

CREATE GENERATOR

CREATE EXCEPTION

See Also

Advance To: