5.6 Condition handling

---

5.6 Condition handling

This section discusses the various methods of implementing condition handling conversion.

5.6.1 Condition handling in stored procedure

Both PL/SQL and SQL PL support EXCEPTION HANDLERS to trap SQL errors and handle them. This mechanism permits separation of a procedure's error processing from its main logic.

PL/SQL uses the following syntax for EXCEPTION:

    EXCEPTION       WHEN exception_name1 THEN <executable statements>       WHEN exception_name2 THEN <executable statements>       ...       WHEN exception_nameN THEN <executable statements>       WHEN OTHER <executable statements> ; 

Where exception_name is one of the predefined exceptions (NO_DATA_FOUND, TOO_MANY_ROWS) or has been defined using the following syntax:

    exception_name EXCEPTION;       PRAGMA EXCEPTION_INIT(exception_name, SQLCODE); 

In DB2, SQL procedures exception handling is accomplished through the use of condition handlers.

A condition handler is an SQL statement that is executed when a specified condition is encountered during execution of a statement within the body of a procedure. The handler is declared within a compound statement, and the handler's scope is limited to that compound statement.

The following is the syntax for condition handler declaration:

    DECLARE {CONTINUE | EXIT | UNDO} HANDLER FOR <condition>                 SQL-procedure-statement; 

where <condition> is one of the following:

• SQLSTATE value

• SQLEXEPTION (SQLCODE < 0)

• SQLWARNING (SQLCODE > 0)

• NOT FOUND

• Condition name

  Warning 

  In DB2, an INSERT, UPDATE or DELETE, which affects no rows also results in a NOT FOUND condition (+100). In absence of a NOT FOUND handler, the SQLWARNING handler will be invoked.

Based on this, to convert the following PL/SQL code to DB2:

    EXCEPTION      WHEN NO_DATA_FOUND THEN v_status :=0;      WHEN OTHER   THEN v_err_flag :=1; 

Two condition handlers need to be declared:

    DECLARE CONTINUE HANDLER FOR NOT FOUND      SET v_status   = 0;    DECLARE CONTINUE HANDLER FOR SQLEXCEPTION      SET v_err_flag = 1; 

In the above example, Oracle exception name corresponds to a DB2 condition name, and the predefined NO_DATA_FOUND exception corresponds to the NOT FOUND condition. To match other Oracle predefined exceptions, the appropriate DB2 SQLSTATE values would have to be used, and the condition would be defined using the following syntax:

    DECLARE  condition_name CONDITION FOR SQLSTATE value; 

For example, Oracle predefined exception TOO_MANY_ROWS can be converted using the following statement:

    DECLARE too_many_rows CONDITION FOR SQLSTATE '21000'; 

then a HANDLER for this CONDITION can be declared as follows:

    DECLARE EXIT HANDLER FOR too_many_rows    BEGIN    ...    END; 

The following procedure (Example 5-22) is to update title_desc column declared as char(50). If in_title_desc is longer then 50 characters, SQLEXCEPTION value is too long would occur and invoke the declared HANDLER. As a result, the table will not be updated and err_num = -433 will be returned as an output parameter.

Example 5-22: Condition handler - SQL EXCEPTION

 CREATE PROCEDURE new_title ( IN in_title_desc varchar(100)                             ,OUT err_num INT ) LANGUAGE SQL P1: BEGIN     DECLARE SQLCODE INTEGER DEFAULT 0;     DECLARE SQLSTATE CHAR(5) DEFAULT ' ';     DECLARE CONTINUE HANDLER FOR SQLEXCEPTION, SQLWARNING       SET err_num = SQLCODE;     UPDATE books     SET title_desc = in_title_desc     WHERE author = 'JACK LONDON'; END P1 

The example below demonstrates the usage of CONDITION HANDLERS in SQL procedures.

Note 

This example uses a CONTINUE handler. That is, after the handler logic is complete, the flow of control continues from where the condition originally occurred. Without the continue handler, the procedure would have exited early, and returned an exception to the application.

Now, the above procedure can be changed to HANDLE a long value of title_desc and, should a value longer than 50 characters occur, update the books table with a truncated value. See Example 5-23.

Example 5-23: Condition handler - handle a long value

 CREATE PROCEDURE new_title_1 ( IN in_title_desc VARCHAR(100)                                ,OUT message_out CHAR(70) ) LANGUAGE SQL P1: BEGIN     DECLARE SQLCODE INTEGER  DEFAULT 0;     DECLARE SQLSTATE CHAR(5) DEFAULT ' ';     DECLARE v_trunc INT      DEFAULT 0;     DECLARE value_error CONDITION FOR SQLSTATE '22001';     DECLARE CONTINUE HANDLER FOR value_error     BEGIN        UPDATE books        SET title_desc = substr(in_title_desc,1,50)        WHERE author = 'JACK LONDON';        SET v_trunc = 1;     END;     UPDATE books     SET title_desc = in_title_desc     WHERE author = 'JACK LONDON';     IF v_trunc = 0 THEN        SET message_out = 'TITLE UPDATED WITHOUT PROBLEM';     ELSE        SET message_out = 'TITLE UPDATED WITH TRUNCATION' ;     END IF; END P1 

5.6.2 Condition handling in triggers and functions

As discussed section 5.2, "Comparing SQL PL and inline SQL PL" on page 152, triggers and functions use a subset of the full SQL PL language called inline SQL PL. Unfortunately, condition handling is not supported in triggers or functions. However, there are often cases where condition handlers may not be required. Here we present some examples.

Consider the following Oracle function, which contains a condition handler:

    CREATE OR REPLACE FUNCTION func_with_handler1    RETURN NUMBER    AS       v_id NUMBER;    BEGIN       BEGIN          SELECT ObjID          INTO v_id          FROM T1;          EXCEPTION WHEN OTHERS THEN          v_Id := 0;       END;       RETURN v_id;    END; 

When converted through MTK, this function is converted as a stored procedure because a condition handler is not supported in DB2 functions. In some cases, this may be the appropriate conversion. If this function were used in an SQL statement however, we would need to make an effort to retain it as a function in DB2.

Upon closer examination of this function, you can see that the function can be rewritten without handlers provided that the column ObjID in table T1 is not nullable. It can be rewritten as follows:

    CREATE FUNCTION func_with_handler1()    RETURNS INT    BEGIN ATOMIC        DECLARE v_id INT;       SET (v_id) = (SELECT ObjID FROM T1);       IF (v_id = NULL) THEN           SET v_id = 0;       END IF;       RETURN v_id;    END 

The value of v_id is set to null if the SELECT statement returns no rows. Assuming the column ObjID is not nullable, if v_id is null after SELECT, it must be the case that no rows were returned. The same technique can be applied to triggers.

Of course, it could very well be that the logic is so complex that such a simple substitution is not possible. In that case, the other option is to change your application so that the function operates without handlers. If an error does occur, then the exception will be returned to the function caller (be it a stored procedure or the application itself) for handling.

5.6.3 Converting RAISE_APPLICATION_ERROR

Oracle PL/SQL permits issuance of user-defined error messages from stored procedures using RAISE_APPLICATION_ERROR. Thus, errors can be reported to the application. To call raise_application_error, the following syntax can be used:

    raise_application_error(error_number, message[, {TRUE | FALSE}]); 

DB2 SQL procedures support SIGNAL SQLSTATE statements to provide a similar functionality.

Example 5-24 shows how to rewrite new_title procedure to report a detection of input value longer than 50 characters.

Example 5-24: Condition handler - SIGNAL SQLSTATE

    CREATE PROCEDURE new_title_2 (IN in_title_desc VARCHAR(100))    LANGUAGE SQL    P1: BEGIN        DECLARE SQLCODE INTEGER   DEFAULT 0;        DECLARE SQLSTATE CHAR(5)  DEFAULT ' ';        IF (length(in_title_desc) > 50) THEN           SIGNAL SQLSTATE '71001'           SET MESSAGE_TEXT = 'Value for update is too long';        ELSE           UPDATE books           SET title_desc = in_title_desc           WHERE author = 'JACK LONDON';        END IF;    END P1 

In ORACLE PL/SQL exception handler, functions SQLCODE and SQLERRM can be used to determine which error occurred and to get the associated error message.

DB2 SQL PL supports the GET DIAGNOSTICS statement to obtain information related to the SQL statement just executed. This statement can be used within CONDITION HANDLER declaration to return message associated with the error:

    DECLARE EXIT HANDLER FOR SQLEXCEPTION    GET DIAGNOSTICS EXCEPTION 1 out_err_msg = MESSAGE_TEXT; 

Together with the error message, GET DIAGNOSTICS permits retrieval of the number of rows that were affected by the previous UPDATE, DELETE, or INSERT statement:

    GET DIAGNOSTICS v_rowcount = ROW_COUNT; 

The RETURN value of a nested procedure can be retrieved by using:

    GET DIAGNOSTICS v_retcode = RETURN_STATUS; 

---