DB2 FOR LUW / DB2FORLUW / A50 - 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 LUW V105 fix pack 3                           * 
****************************************************************

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

DB2 Version 10.5 Fix Pack 3 for Linux, UNIX, and Windows
 DB2 Version 10.5 Fix Pack 3a for Linux, UNIX, and Windows
 DB2 Cancun Release 10.5.0.4 (also known as Fix Pack 4) for Linux, UNIX, and Windows
 DB2 Version 10.5 Fix Pack 9 for Linux, UNIX, and Windows
 

DB2 LUW v105 fix pack 3 an error SQL0313N will be returned when 
number of parameters in OPEN statement will not match number of 
parameters that parametarized cursor was declared with

not known / see Local fix

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

21.10.2013
27.02.2014
27.02.2014