Handling Exceptions (Run Time Errors) in SQL Scripts

This article would demonstrate how to handle Exceptions in CIS.

There are four attributes of an exception in the Exception handler.
1. CURRENT_EXCEPTION.NAME – VARCHAR(255) – Exception’s Name
2. CURRENT_EXCEPTION.MESSAGE – VARCHAR(255) – Value Defined for the current exception – Can be NULL
3. CURRENT_EXCEPTION.ID – INTEGER – An Integer that is the exception ID
4. CURRENT_EXCEPTION.TRACE – VARCHAR(32768) – The Exception stack trace (Detailed Error Message)

Additionally Exceptions can be declared to be handled by the SQL Script itself.

Eg:

PROCEDURE v_testExc (IN x INTEGER, OUT result VARCHAR(32768))
     BEGIN
     DECLARE illegal_arg_ex EXCEPTION; --declaring an exception
             IF x < 0 THEN
                RAISE illegal_arg_ex; --raising an exception when x is less than 0
             END IF;
             CALL PRINT(CAST(x/0 AS VARCHAR)); --Raise a run time error
             SET result = 'SUCCESS';
     EXCEPTION
             WHEN illegal_arg_ex THEN
             SET result = 'FAILURE - x value cannot be less than 0';
     ELSE
             IF CURRENT_EXCEPTION.MESSAGE IS NOT NULL THEN
                SET result = CURRENT_EXCEPTION.MESSAGE;
             ELSE
                SET result = CURRENT_EXCEPTION.NAME;
             END IF;
END

In the above script we are declaring an exception illegal_arg_ex and raising it only when x value is less than 0. We are also raising a run time error by dividing the value of x by 0 which is not allowed.

In the Exception block we set the value of result as ‘FAILURE – x value cannot be less than 0′ when the illegal_arg_ex is raised also we capture all the other exceptions that can be raised during exception of the SQL Script by using the ELSE clause in the EXCEPTION block.

We set the value of result to CURRENT_EXCEPTION.MESSAGE if it is NOT NULL or CURRENT_EXCEPTION.NAME if the CURRENT_EXCEPTION.MESSAGE is NULL. Hence we capture all the exceptions in the SQL Script.

If you run the SQL SCRIPT with a value of a negative number
result = ‘FAILURE – x value cannot be less than 0′

If you enter any positive number
result = ‘A system exception has occurred. Unable to compute divide function for ’1 / 0′ since the denominator cannot be 0 [func-3956000]‘

If you comment out the code CALL PRINT(CAST(x/0 AS VARCHAR)); you will be able to execute the SQL SCRIPT without any exceptions and the value of result = ‘SUCCESS’