DB2 FOR LUW / DB2FORLUW / 970 - DB2

Cursor attributes SQL%FOUND and SQL%NOTFOUND are not updated by 
a SELECT INTO statement, but will retain whatever values they 
held before execution of the SELECT INTO statement.  This is 
incorrect.

**************************************************************** 
* USERS AFFECTED:                                              * 
* All                                                          * 
**************************************************************** 
* PROBLEM DESCRIPTION:                                         * 
* Cursor attributes SQL%FOUND and SQL%NOTFOUND are not updated * 
* by                                                           * 
* a SELECT INTO statement, but will retain whatever values     * 
* they                                                         * 
* held before execution of the SELECT INTO statement.          * 
**************************************************************** 
* RECOMMENDATION:                                              * 
* Upgrade to version 9.7 fixpack 5.                            * 
****************************************************************

In practice, the exception NO_DATA_FOUND will be raised in 
PL/SQL code when a SELECT INTO statement returns no rows.  Any 
subsequent logic that depends upon SQL%NOTFOUND being TRUE or 
SQL%FOUND being FALSE will not normally be executed, unless it 
is part of a handler that catches the NO_DATA_FOUND exception. 
 
As a workaround, move any logic that handles the cases where 
(SQL%NOTFOUND = TRUE) or (SQL%FOUND = FALSE) to the exception 
handling section.  Remove any tests for (SQL%FOUND = TRUE) or 
(SQL%NOTFOUND = FALSE) that test the result of a previous SELECT 
INTO statement.  Such tests are redundant and may produce 
incorrect results, because SQL%FOUND and SQL%NOTFOUND are not 
updated by the SELECT INTO statement. 
 
For example, rewrite the following logic: 
 
BEGIN 
  ... 
  select * into v_rec from test where id = 6; 
  IF SQL%NOTFOUND = TRUE THEN 
    -- The following code is unreachable: 
    -- Exception handling takes precedence. 
    DBMS_OUTPUT.PUT_LINE('No data in V_REC'); 
  ELSE 
    DBMS_OUTPUT.PUT_LINE('V_REC data read successfully'); 
  END IF; 
  ... 
END; 
 
as: 
 
 
BEGIN 
  ... 
  select * into v_rec from test where id = 6; 
  -- There is no need to test SQL%FOUND. 
  -- The fact that this code is reached 
  -- implies that SQL%FOUND is TRUE. 
  DBMS_OUTPUT.PUT_LINE('V_REC data read successfully'); 
  ... 
EXCEPTION 
  WHEN NO_DATA_FOUND THEN 
    DBMS_OUTPUT.PUT_LINE('No data in V_REC'); 
END;

DB2 Version 9.7 Fix Pack 5 for Linux, UNIX, and Windows
 DB2 Version 9.7 Fix Pack 6 for Linux, UNIX, and Windows
 DB2 Version 9.7 Fix Pack 7 for Linux, UNIX, and Windows
 DB2 Version 9.7 Fix Pack 8 for Linux, UNIX, and Windows
 DB2 Version 9.7 Fix Pack 9 for Linux, UNIX, and Windows
 DB2 Version 9.7 Fix Pack 9a for Linux, UNIX, and Windows
 DB2 Version 9.7 Fix Pack 10 for Linux, UNIX, and Windows
 

The problem was first fixed in V97 FP5

not known / see Local fix

Date  - problem reported    :
Date  - problem closed      :
Date  - last modified       :

02.06.2011
09.01.2012
09.01.2012

9.7.FP5