Stored Procedure Exceptions
Go Up to Working with Stored Procedures
Contents
An exception is a named error message that can be raised from a stored procedure. Exceptions are created with CREATE EXCEPTION, modified with ALTER EXCEPTION, and dropped with DROP EXCEPTION. A stored procedure raises an exception with EXCEPTION
<name>.
When raised, an exception returns an error message to the calling program and terminates execution of the procedure that raised it, unless the exception is handled by a WHEN statement.
Like procedures, exceptions are created and stored in a database, where they can be used by any procedure that needs them. Exceptions must be created and committed before they can be raised.
For more information on raising and handling exceptions, see Raising an Exception in a Stored Procedure.
Creating Exceptions
To create an exception, use the following CREATE EXCEPTION syntax:
CREATE EXCEPTION name '<message>';
For example, the following statement creates an exception named
REASSIGN_SALES:
CREATE EXCEPTION REASSIGN_SALES 'Reassign the sales records
before deleting this employee.';
Altering Exceptions
To change the message returned by an exception, use the following syntax:
ALTER EXCEPTION name '<message>';
Only the creator of an exception can alter it. For example, the following statement changes the text of the exception created in the previous section:
ALTER EXCEPTION REASSIGN_SALES 'Can’t delete employee--Reassign
Sales';
You can alter an exception even though a database object depends on it. If the exception is raised by a trigger, you cannot drop the exception unless you first drop the trigger or stored procedure. Use ALTER EXCEPTION instead.
Dropping Exceptions
To delete an exception, use the following syntax:
DROP EXCEPTION name;
For example, the following statement drops the exception, REASSIGN_SALES:
DROP EXCEPTION REASSIGN_SALES;
The following restrictions apply to dropping exceptions:
- Only the creator of an exception can drop it.
- Exceptions used in existing procedures and triggers cannot be dropped.
- Exceptions currently in use cannot be dropped.
In isql,
SHOW PROCEDURES displays a list of dependencies, the procedures, exceptions, and tables which the stored procedure uses. SHOW PROCEDURE <name> displays the body and header information for the named procedure. SHOW TRIGGERS <table> displays the triggers defined for <table>. SHOW TRIGGER <name> displays the body and header information for the named trigger.Raising an Exception in a Stored Procedure
To raise an exception in a stored procedure, use the following syntax:
EXCEPTION name;
where <name> is the name of an exception that already exists in the database.
When an exception is raised, it does the following:
- Terminates the procedure in which it was raised and undoes any actions performed (directly or indirectly) by the procedure.
- Returns an error message to the calling application. In isql, the error message is displayed on the screen.
If an exception is handled with a
WHEN statement, it behaves differently. For more information on exception handling, see Handling Exceptions.The following statements raise the exception, REASSIGN_SALES:
IF (any_sales > 0) THEN
EXCEPTION REASSIGN_SALES;