DB2 FOR LUW / DB2FORLUW / A10 - DB2

PL/SQL routine creation should fail when the variables passed in 
the cursor statement do not match those in 
the for loop.  Routines created with this syntax will appear to 
work and will run.  However they will not return any data 
because the loop will not execute as expected. 
 
Below is an example of a procedure that will encounter the 
issue. 
 
  CREATE OR REPLACE PROCEDURE myproc (p_id in number, p_txt 
varchar2) is 
  cursor c(c_id in number, c_txt varchar2) is 
  select id, txt from t1 where id = c_id and txt = c_txt; 
  v_txt varchar2(10); 
  begin 
  for r in c loop 
   v_txt := r.txt; 
   dbms_output.put_line(v_txt); 
  end loop; 
  end; 
 
In the above example the cursor had two input variables c_id and 
c_txt while the for loop did not use any variables.  For the 
procedure to work as expected the for loop should have been set 
as follows. 
 
for r in c(p_id, p_txt) loop

**************************************************************** 
* USERS AFFECTED:                                              * 
* DB2 LUW                                                      * 
**************************************************************** 
* PROBLEM DESCRIPTION:                                         * 
* See Error Description                                        * 
**************************************************************** 
* RECOMMENDATION:                                              * 
* upgrade to DB2 version 10.1 fixpack 3                        * 
****************************************************************

NONE. 
Modify the routine so that the variables in the FOR LOOP match 
those in the CURSOR

DB2 Version 10.1 Fix Pack 3 for Linux, UNIX, and Windows
 DB2 Version 10.1 Fix Pack 4 for Linux, UNIX, and Windows
 DB2 Version 10.1 Fix Pack 3a for Linux, UNIX, and Windows
 DB2 Version 10.1 Fix Pack 6 for Linux, UNIX, and Windows
 

DB2 LUW version 10.1 fixpack 3 
an error will  be returned if number of parameters in open 
statement does not match declaration of the cursor.

not known / see Local fix

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

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

16.01.2013
18.10.2013
18.10.2013