home clear 64x64
en blue 200x116 de orange 200x116 info letter User
suche 36x36
Latest versionsfixlist
11.1.0.7 FixList
10.5.0.9 FixList
10.1.0.6 FixList
9.8.0.5 FixList
9.7.0.11 FixList
9.5.0.10 FixList
9.1.0.12 FixList
Have problems? - contact us.
Register for free anmeldung-x26
Contact form kontakt-x26

DB2 - Problem description

Problem IC66926 Status: Closed

DB2 CAN NOT HOOK NO_DATA_FOUND INSIDE OF A FOR CURSOR LOOP IN PL/SQL.

product:
DB2 FOR LUW / DB2FORLUW / 980 - DB2
Problem description:
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.
Problem Summary:
**************************************************************** 
* 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.                   * 
****************************************************************
Local Fix:
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.
available fix packs:
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

Solution
Problem was first fixed in DB2 UDB Version 9.8 Fix Pack 3.
Workaround
not known / see Local fix
Timestamps
Date  - problem reported    :
Date  - problem closed      :
Date  - last modified       :
05.03.2010
08.11.2012
08.11.2012
Problem solved at the following versions (IBM BugInfos)
9.7.FP3,
9.8.FP3
Problem solved according to the fixlist(s) of the following version(s)
9.8.0.3 FixList