Managing Exception Conditions
An exception is raised if an error occurs when executing an SQL statement. Every exception is identified by an exception condition, expressed in terms of its
SQLSTATEvalue is represented by the keyword
SQLSTATEfollowed by a 5-character string containing only uppercase alphanumeric characters. The first two characters of the string identify the exception class and the last three the exception sub-class.
In Mimer SQL, the range of possible
SQLSTATEvalues is divided into standard values and implementation-defined values. The implementation-defined values are those beginning with the characters
8-9. For a list of the values, see Mimer SQL Reference Manual, Appendix E, SQLSTATE Return Codes.
Whenever an exception is raised, the exception condition is placed in the diagnostics area and the
SQLSTATEvalue can be retrieved by using the
RETURNED_SQLSTATEoption of the
In addition to expressing an exception condition in terms of its
SQLSTATEvalue, it is possible (within a compound SQL statement) to declare a condition name to represent it.
Whenever a condition name is used, it is immediately translated into the
SQLSTATEvalue it represents. For more information, see Declaring Condition Names.
It is possible to raise an exception without an error occurring by using the
SIGNALstatement. When the
SIGNALstatement is used, the specified exception condition is placed in the cleared diagnostics area, expressed as its
SQLSTATEvalue, and control proceeds as if an error had just occurred.
It possible to return specific error messages with the SIGNAL statement by using the optional SET clause.
ExampleSIGNAL SQLSTATE 'UE456' SET message_text = 'The specified horse, ' || horse || ' does not exist in the database';
Exception Handlers and Actions
It is possible to declare exception handlers in a compound SQL statement that perform some action when exceptions are raised. The action defined by the exception handler is associated with one or more specific exception conditions, or one or more exception class groups, specified when the exception handler is declared. For more information, see Declaring Exception Handlers.
If there is an exception handler action defined for an exception condition that is raised, the exception handler action is performed and execution continues in the manner defined by the type of the exception handler.
If no exception handler action has been defined for an exception condition that is raised, the default error handling mechanism is invoked (which usually makes the exception condition visible to the calling environment).
If the exception
NOT FOUNDor an
SQLWARNINGis raised in an unhandled situation, execution will continue and the exception will be cleared by execution of the next statement in the procedure. The
GET DIAGNOSTICSstatement can be used to test for the
NOT FOUNDexception and an
It may be necessary for an exception handler action to re-raise the current exception condition or to raise an alternative exception condition. The
RESIGNALstatement is provided for this purpose and it may only be executed from within an exception handler.
RESIGNALis executed without specifying an exception condition, the current exception condition remains in the diagnostics area and the error handling mechanism proceeds to deal with the error as if the current exception handler action had not been found.
If an exception condition is specified (in the same way as for
SIGNAL), this is pushed onto the top of the stack of exceptions in the diagnostics area, becoming the current
SQLSTATEvalue, and the error handling mechanism proceeds as just described.
The size of the exceptions stack in the diagnostics area is set by using the
SET TRANSACTION DIAGNOSTICS SIZEstatement, see Exception Diagnostics Within Transactions.
RESIGNALis useful in situations where there are nested exception handler actions defined and it is required that an enclosing exception handler action be invoked from an inner one, or where the default error handling mechanism is to be allowed to proceed from some point within a defined exception handler action. As with the SIGNAL statement it is possible to supply a specific message text.
Examples:RESIGNAL; RESIGNAL SQLSTATE 'UE456' SET message_text = 'The horse ' || horse || ' does not exist';
Declaring Condition Names
As discussed in the previous section, exception conditions are identified by an
SQLSTATEvalue. Whenever an exception is raised, the exception condition that identifies it is stored in the diagnostics area in the form of its
It is always possible to specify an exception condition by using its
SQLSTATE VALUE 'S0700', however it is often desirable to declare a condition name that represents the
SQLSTATEvalue in a way that more meaningfully describes the exception.
Condition names may be declared in a compound SQL statement, see the Mimer SQL Reference Manual, COMPOUND STATEMENT, for a detailed description.
ExampleDECLARE invalid_parameter CONDITION FOR SQLSTATE 'UE456'; ... SIGNAL invalid_parameter;
Following this declaration, the condition name
INVALID_PARAMETERcan be used instead of the
SQLSTATE VALUE 'UE456'whenever there is a need to refer to this exception condition.
If a condition name is used in a signal statement the associated
SQLSTATEvalue and the condition name is placed in the diagnostics area. If the condition does not have an associated
45000is used. A condition is always local to a routine, i.e. consider the following example:create procedure p2() begin declare condition c1; ... signal c1; end create procedure p1() begin declare condition c1; declare exit handler for c1 begin ... call p2(); ... end end
In this case the exit handler in the procedure
p1will not be invoked when the statement signal
c1is executed.In order to catch a signaled condition the associated
SQLSTATEmust be used. The condition identifier can be propagated by using a
SQLSTATEvalues in Mimer SQL that lie outside the range of standard values are treated as implementation-defined, so all
SQLSTATEvalues are handled in the same way and may be specified explicitly in all situations.
Declaring Exception Handlers
Exception handlers may be declared in a compound SQL statement in order to define an action which will be executed if specified exceptions are raised within the scope of the exception handler.
The structure of the handler action is the same as the body of a routine, i.e. a single executable procedural SQL statement. The exceptions to which the handler action will respond may be specified as a list of exception conditions or by specifying one or more exception class groups.
The exception class groups are:
SQLSTATEvalues beginning with
SQLSTATEvalues beginning with
SQLEXCEPTIONcovers all other
SQLSTATEvalues (including those in the implementation defined range), excluding those beginning with
An exception handler that is declared to respond to one or more exception class groups is referred to as a general exception handler.
An exception condition may be specified by its
SQLSTATEvalue or a condition name declared to represent it. An exception handler which is declared to respond to one or more specific exception conditions is referred to as a specific exception handler.
The same exception condition must not be specified more than once in the same exception handler declaration.
An exception handler can either be a general exception handler or a specific exception handler, i.e. an exception handler declaration cannot contain both exception class groups and specific exception conditions.
Exception handlers are declared in the local handler declaration list of a compound SQL statement and the scope of an exception handler is that compound SQL statement plus all the SQL statements contained within it except when another routine is invoked. When a user defined routine is invoked all exception handlers in the calling routine will get out of scope and they will get into scope again when the invoked routine has finished executing, e.g:CREATE PROCEDURE innerMost(INT x) BEGIN -- no handlers in this routine IF x > 0 THEN SIGNAL SQLSTATE 'UE345'; ELSE SIGNAL SQLSTATE 'UE543'; END IF; END CREATE PROCEDURE outerMost() BEGIN DECLARE EXIT HANDLER FOR SQLSTATE 'UE345' BEGIN END; DECLARE CONTINUE HANDLER FOR SQLSTATE 'UE543' BEGIN END; CALL innerMost(0); CALL innerMost(1); CALL innerMost(2); END
When the signal statement with exception
innerMostroutine is executed, the execution of this routine will be stopped as there is no handlers declared. The exception will the be propagated to the
outerMostroutine which has a continue handler for this exception. This means that the execution will proceed with the next call statement. This will cause a new exception (UE345) being signalled. Again this exception will be propagated to the calling routine and the first exception handler will be invoked. As this is an exit handler the execution will continue after the end of the compound statement in the
outerMostroutine, i.e. the statement
call innerMost(2)will never be executed.
The exception handler will be executed if one of the exceptions it is declared to respond to is raised within the scope of the handler.
A local handler declaration list can only contain one exception handler declared to respond to a particular exception condition or exception class group.
It is possible to declare a general and a specific exception handler, both of which cover the same scope, where an exception condition specified for the specific handler is in one of the exception class groups specified for the general handler. If the exception condition is raised in this situation, the specific handler is executed in preference to the general handler.
It is possible for the scope of two specific exception handlers, which respond to the same exception condition, to overlap. This will be the case if there are two nested compound SQL statements and each declares a specific exception handler for the same exception condition (this is permitted, provided the two exception handlers are not declared in the same local handler declaration list). In this situation the innermost exception handler action will be executed.
The same is true for two general exception handlers in this situation.
RESIGNALstatement can be used in situations like this, in the inner exception handler action, to get the outer exception handler action to execute by propagating the exception out from the exception handler action which is currently executing.
Types of Exception Handlers
Exception handlers fall into the following types:
- Exit Handler
This type of exception handler will execute when the exception condition(s) that apply to it are raised. After the handler has executed, flow of control exits the scope of the compound SQL statement containing the exception handler declaration, by effectively performing a
LEAVE, see Scope in Routines - the Compound SQL Statement.
- Continue Handler
This type of exception handler will execute when the exception condition(s) that apply to it are raised. After the handler has executed, flow of control continues by executing the SQL statement immediately following the SQL statement that raised the exception.
- Undo Handler
The execution of this type of handler will be initiated when the exception condition(s) that apply to it are raised. Before the handler action executes, all changes made by the executed SQL statements in the compound SQL statement, or by any SQL statements triggered by them, are canceled. The handler action is then executed and flow of control exits the scope of the compound SQL statement containing the exception handler declaration, by effectively performing a
LEAVE, see Scope in Routines - the Compound SQL Statement.
- Note: An
UNDOexception handler can only be declared in a compound SQL statement that has been defined as
ATOMIC, see The ATOMIC Compound SQL Statement.
Examples of Exception Handlerss1: BEGIN DECLARE EXIT HANDLER FOR SQLEXCEPTION BEGIN ... END; ... END s1; s2: BEGIN DECLARE CONTINUE HANDLER FOR SQLSTATE 'S0700' BEGIN ... END; ... END s2; s3: BEGIN DECLARE EXIT HANDLER FOR SQLSTATE 'S0700' BEGIN ... END; ... s4: BEGIN ATOMIC DECLARE UNDO HANDLER FOR SQLSTATE 'S0700' BEGIN ... END; ... END s4; ... END s3;
Using the GET DIAGNOSTICS Statement
GET DIAGNOSTICSstatement can be used in an exception handler to get the specific
SQLSTATEvalue that provoked execution of the exception handler.
ExampleDECLARE CONTINUE HANDLER FOR SQLEXCEPTION BEGIN DECLARE v_state CHAR(5) DEFAULT '?????'; GET DIAGNOSTICS EXCEPTION 1 v_state = RETURNED_SQLSTATE; CASE v_state WHEN '22003' THEN ... WHEN '20000' THEN ... ELSE RESIGNAL; END CASE; END; -- of sqlexception handler
GET DIAGNOSTICSmust be the first statement in the exception handler as the diagnostics area always contains information about the latest statement.
GET DIAGNOSTICSstatement can also be used to get information about warnings and not found exceptions.
ExampleSELECT format into v_format FROM formats where category_id = v_category_id; GET DIAGNOSTICS EXCEPTION 1 v_state = RETURNED_SQLSTATE; IF v_state = '02000' THEN -- not found ELSE -- found END IF;
As mentioned, to describe an error always use
GET DIAGNOSTICSin an exception handler. I.e. it is not meaningful to place
GET DIAGNOSTICSafter a statement to check for errors. Example:BEGIN DECLARE v_state CHAR(5); DECLARE EXIT HANDLER FOR sqlexception BEGIN ... END; INSERT INTO format(format,category_id) VALUES (v_format,v_category_id); GET DIAGNOSTICS EXCEPTION 1 v_state = RETURNED_SQLSTATE; END
If an exception occurs in the
INSERTstatement this will be catched by the exception handler and as this is an exit handler the execution will resume after the compound statement. Thus, the diagnostics statements will never be invoked in this case. Even if it is a continue handler the
GET DIAGNOSTICSstatement is superfluous as the handler would clear the diagnostics information.
Mimer Information Technology AB
Voice: +46 18 780 92 00
Fax: +46 18 780 92 40