Informix Guide to SQL: Syntax SPL Statements

Use the ON EXCEPTION statement to specify the actions that are taken for a particular error or a set of errors.

The ON EXCEPTION statement, together with the RAISE EXCEPTION statement, provides an error-trapping and error-recovery mechanism for SPL. The ON EXCEPTION statement defines a list of errors that you want to trap as the SPL routine executes and specifies the action (within the statement block) to take when the trap is triggered. If the IN clause is omitted, all errors are trapped.

You can use more than one ON EXCEPTION statement within a given statement block.

The scope of an ON EXCEPTION statement is the statement block that follows the ON EXCEPTION statement and all the statement blocks that are nested within that following statement block.

The exceptions that are trapped can be either system- or user-defined.

When an exception is trapped, the error status is cleared.

If you specify a variable to receive an ISAM error, but no accompanying ISAM error exists, a zero (0) returns to the variable. If you specify a variable to receive the returned error text, but none exists, an empty string goes into the variable.

An ON EXCEPTION statement does not work in a udr that is called by a trigger.

ON EXCEPTION is a declarative statement, not an executable statement. For this reason, you must use the ON EXCEPTION statement before any executable statement and after any DEFINE statement in an SPL routine.

The following example shows the correct placement of an ON EXCEPTION statement. The add_salesperson function inserts a set of values into a table. If the table does not exist, it is created, and the values are inserted. The function also returns the total number of rows in the table after the insert occurs.

CREATE FUNCTION add_salesperson(last CHAR(15),

				 first CHAR(15))

	RETURNING INT;

	DEFINE x INT;

	ON EXCEPTION IN (-206) -- If no table was found, create one

		CREATE TABLE emp_list		 	

			 (lname CHAR(15),fname CHAR(15), tele CHAR(12));

		INSERT INTO emp_list VALUES -- and insert values

			 (last, first, '800-555-1234');

	END EXCEPTION WITH RESUME

	INSERT INTO emp_list VALUES (last, first, '800-555-1234')

	LET x = SELECT count(*) FROM emp_list;

	RETURN x;

END FUNCTION

When an error occurs, the database server searches for the last declaration of the ON EXCEPTION statement, which traps the particular error code. The ON EXCEPTION statement can have the error number in the IN clause or have no IN clause. If the database server finds no pertinent ON EXCEPTION statement, the error code passes back to the caller (the SPL routine, application, or interactive user), and execution aborts.

The following example uses two ON EXCEPTION statements with the same error number so that error code 691 can be trapped in two levels of nesting:

CREATE PROCEDURE delete_cust (cnum INT)

	ON EXCEPTION  IN (-691)    -- children exist

		BEGIN -- Begin-end is necessary so that other DELETEs

			 -- don't get caught in here.

        	 	 	ON EXCEPTION IN (-691)

            	 	 		DELETE FROM another_child WHERE num = cnum;

    	    	 	 	DELETE FROM orders WHERE customer_num = cnum;

			END EXCEPTION -- for 691



    		DELETE FROM orders WHERE customer_num = cnum;

		END



      DELETE FROM cust_calls WHERE customer_num = cnum;

      DELETE FROM customer WHERE customer_num = cnum;

    END EXCEPTION

       DELETE FROM customer WHERE customer_num = cnum;

END PROCEDURE

A trap is triggered if either the SQL error code or the ISAM error code matches an exception code in the list of error numbers. The search through the list begins from the left and stops with the first match.

You can use a combination of an ON EXCEPTION statement without an IN clause and one or more ON EXCEPTION statements with an IN clause to set up default trapping. When an error occurs, the database server searches for the last declaration of the ON EXCEPTION statement that traps the particular error code.

CREATE PROCEDURE ex_test ()

	DEFINE error_num INT;

	.

	.

	.

	ON EXCEPTION

	SET error_num

	-- action C

	END EXCEPTION



	ON EXCEPTION IN (-300)

	-- action B

	END EXCEPTION

	ON EXCEPTION IN (-210, -211, -212)

	SET error_num

	-- action A

	END EXCEPTION

	.

	.

	.

A summary of the sequence of statements in the previous example would be:

1. Test for an error.
2. If error -210, -211, or -212 occurs, take action A.
3. If error -300 occurs, take action B.
4. If any other error occurs, take action C.

If you use the SET clause, when an exception occurs, the SQL error code and (optionally) the ISAM error code are inserted into the variables that are specified in the SET clause. If you provided an error_data_var, any error text that the database server returns is put into the error_data_var. Error text includes information such as the offending table or column name.

The first example in Placement of the ON EXCEPTION Statement uses the WITH RESUME keyword to indicate that after the statement block in the ON EXCEPTION statement executes, execution is to continue at the LET x = SELECT COUNT(*) FROM emp_list statement, which is the line following the line that raised the error. For this function, the result is that the count of salespeople names occurs even if the error occurred.

If you do not include the WITH RESUME keyword in your ON EXCEPTION statement, the next statement that executes after an exception occurs depends on the placement of the ON EXCEPTION statement, as the following scenarios describe:

• If the ON EXCEPTION statement is inside a statement block with a BEGIN and an END keyword, execution resumes with the first statement (if any) after that BEGIN...END block. That is, it resumes after the scope of the ON EXCEPTION statement.
• If the ON EXCEPTION statement is inside a loop (FOR, WHILE, FOREACH), the rest of the loop is skipped, and execution resumes with the next iteration of the loop.
• If no statement or block, but only the SPL routine, contains the ON EXCEPTION statement, the routine executes a RETURN statement with no arguments to terminate. That is, the routine returns a successful status and no values.

To prevent an infinite loop, if an error occurs during execution of the statement block of an error trap, the search for another trap does not include the current trap.