DB2 FOR LUW / DB2FORLUW / 970 - DB2

The following PL/SQL script does not display the "Bottom of FOR 
loop" message.  Assume that table TEST2 contains one or more 
rows, and that TEST1 is empty: 
 
create or replace procedure cursor_test 
is 
        cursor c2 is 
                select col1 from test2; 
        testv   varchar2(10); 
begin 
        for c2_rec in c2 loop 
                dbms_output.put_line('Top of FOR loop'); 
                begin 
                        select col1 into testv from test1; 
                exception 
                        when no_data_found then 
                                dbms_output.put_line('NO DATA 
FOUND'); 
                        when others then 
                                null; 
                end; 
                dbms_output.put_line('Bottom of FOR loop'); 
        end loop; 
end; 
/ 
 
After the first execution of the SELECT ... INTO statement, the 
FOR loop exits prematurely and the exception clause for 
NO_DATA_FOUND is not run.

**************************************************************** 
* USERS AFFECTED:                                              * 
* ALL.                                                         * 
**************************************************************** 
* PROBLEM DESCRIPTION:                                         * 
* See the APAR description.                                    * 
**************************************************************** 
* RECOMMENDATION:                                              * 
* Upgrade to DB2 UDB version 9.7 fixpack 2.                    * 
****************************************************************

Wrap the SELECT ... INTO statement in a separate PL/SQL 
function, then invoke this function to obtain the column value; 
for example: 
 
create or replace function get_test1_col1 return varchar2(2) 
as 
  declare v varchar2(2); 
begin 
  select col1 into v from test1; 
  return v; 
end; 
/ 
 
-- In the calling procedure... 
 
  for c2_rec in c2 loop 
    dbms_output.put_line('Top of FOR loop'); 
 
    begin 
      testv := get_test1_col1; 
    exception 
      when no_data_found then 
        dbms_output.put_line('NO_DATA_FOUND'); 
      when others then 
        null; 
    end; 
 
This will produce the intended behaviour.

DB2 Version 9.7 Fix Pack 2 for Linux, UNIX, and Windows
 DB2 Version 9.7 Fix Pack 3 for Linux, UNIX, and Windows
 DB2 Version 9.7 Fix Pack 3a for Linux, UNIX, and Windows
 DB2 Version 9.7 Fix Pack 4 for Linux, UNIX, and Windows
 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 9a 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 10 for Linux, UNIX, and Windows
 

Problem was first fixed in DB2 UDB Version 9.7 FixPack 2.

See LOCAL FIX.

forerunner  : APAR is sysrouted TO one or more of the following: IC66926 
follow-up : 

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

17.12.2009
17.05.2010
17.05.2010

9.7.FP2