Informix Guide to SQL: Tutorial
Chapter 14: Creating and Using SPL Routines
Home Contents Index Master Index New Book

Exception Handling

You can use the ON EXCEPTION statement to trap any exception (or error) that the database server returns to your SPL routine, or any exception raised by the routine. The RAISE EXCEPTION statement lets you generate an exception within the SPL routine.

In an SPL routine, you cannot use exception handling to handle the following conditions:

Trapping an Error and Recovering

The ON EXCEPTION statement provides a mechanism to trap any error.

To trap an error, enclose a group of statements in a statement block marked with BEGIN and END and add an ON EXCEPTION IN statement at the beginning of the statement block. If an error occurs in the block that follows the ON EXCEPTION statement, you can take recovery action.

Figure 14-107 shows an ON EXCEPTION statement within a statement block.

Figure 14-107

When an error occurs, the SPL interpreter searches for the innermost ON EXCEPTION declaration that traps the error. The first action after trapping the error is to reset the error. When execution of the error action code is complete, and if the ON EXCEPTION declaration that was raised included the WITH RESUME keywords, execution resumes automatically with the statement following the statement that generated the error. If the ON EXCEPTION declaration did not include the WITH RESUME keywords, execution exits the current block entirely.

Scope of Control of an ON EXCEPTION Statement

An ON EXCEPTION statement is valid for the statement block that follows the ON EXCEPTION statement, all the statement blocks nested within the following statement block, and all the statement blocks that follow the ON EXCEPTION statement. It is not valid in the statement block that contains the ON EXCEPTION statement.

The pseudo code in Figure 14-108 shows where the exception is valid within the routine. That is, if error 201 occurs in any of the indicated blocks, the action labeled a201 occurs.

Figure 14-108

User-Generated Exceptions

You can generate your own error using the RAISE EXCEPTION statement, as the example in Figure 14-109 shows.

Figure 14-109

In Figure 14-109, the ON EXCEPTION statement uses two variables, esql and eisam, to hold the error numbers that the database server returns. The IF clause executes if an error occurs and if the SQL error number is -206. If any other SQL error is caught, it is passed out of this BEGIN...END block to the last BEGIN...END block of the previous example.

Simulating SQL Errors

You can generate errors to simulate SQL errors, as the following example shows. In Figure 14-110, if the user is pault, then the SPL routine acts as if that user has no update privileges, even if the user really does have that privilege.

Figure 14-110

Using RAISE EXCEPTION to Exit Nested Code

Figure 14-111 shows how you can use the RAISE EXCEPTION statement to break out of a deeply nested block.

Figure 14-111

If the innermost condition is true (if aa is negative), then the exception is raised, and execution jumps to the code following the END of the block. In this case, execution jumps to the TRACE statement.

Remember that a BEGIN...END block is a single statement. If an error occurs somewhere inside a block and the trap is outside the block, the rest of the block is skipped when execution resumes and execution begins at the next statement.

Unless you set a trap for this error somewhere in the block, the error condition is passed back to the block that contains the call and back to any blocks that contain the block. If no ON EXCEPTION statement exists that is set to handle the error, execution of the SPL routine stops, creating an error for the routine that is executing the SPL routine.

Informix Guide to SQL: Tutorial, version 9.1
Copyright © 1998, Informix Software, Inc. All rights reserved.