DB2 FOR LUW / DB2FORLUW / 980 - 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:                                         * 
* The following PL/SQL script does not display the             * 
* "BottomofFORloop" message.  Assume that table TEST2 contains * 
* one ormorerows, and that TEST1 is empty:create or            * 
* replaceprocedure cursor_testiscursor c2 isselect col1        * 
* fromtest2;testv  varchar2(10);beginfor c2_rec in             * 
* c2loopdbms_output.put_line('Top of FOR loop');beginselect    * 
* col1into testv from test1;exceptionwhen                      * 
* no_data_foundthendbms_output.put_line('NODATAFOUND');when    * 
* othersthennull;end;dbms_output.put_line('Bottom of FOR       * 
* loop');endloop;end;/After the first execution of the SELECT  * 
* ... INTOstatement,theFOR loop exits prematurely and the      * 
* exceptionclause forNO_DATA_FOUND is not run.                 * 
**************************************************************** 
* RECOMMENDATION:                                              * 
* Upgrade to DB2 UDB version 9.8 fix pack 3.                   * 
****************************************************************

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.8 Fix Pack 3 for Linux, UNIX, and Windows
 DB2 Version 9.8 Fix Pack 4 for AIX and Linux
 DB2 Version 9.8 Fix Pack 5 for AIX and Linux
 

Problem was first fixed in DB2 UDB Version 9.8 Fix Pack 3.

not known / see Local fix

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

05.03.2010
08.11.2012
08.11.2012

9.7.FP3,
9.8.FP3