DB2 FOR LUW / DB2FORLUW / 980 - DB2

If your PL/SQL routine contains two or more FOR loops that use 
the same cursor name, error SQL0502N may be reported by DB2 when 
the second FOR loop is executed.  For example: 
 
set sqlcompat plsql; 
set serveroutput on; 
 
create table test1(col1 varchar2(10)); 
insert into test1 values 'AAA', 'BBB', 'CCC'; 
 
create or replace procedure test 
is 
  cursor C1 is select COL1 from TEST1; 
begin 
  for rec in C1 
  loop 
    dbms_output.put_line(rec.col1); 
  end loop; 
 
  for rec in C1 
  loop 
    dbms_output.put_line(rec.col1); 
  end loop; 
end; 
/ 
 
begin 
  test; 
end; 
/ 
 
DB21034E  The command was processed as an SQL statement because 
it was not a valid Command Line Processor command. 
During SQL processing it returned: 
SQL0502N  The cursor specified in an OPEN statement is already 
open. SQLSTATE=24502

**************************************************************** 
* USERS AFFECTED:                                              * 
* ALL                                                          * 
**************************************************************** 
* PROBLEM DESCRIPTION:                                         * 
* If your PL/SQL routine contains two or more FOR loops        * 
* thatusethe same cursor name, error SQL0502N may be           * 
* reportedby DB2 when the second FOR loop is executed.SQL0502N * 
*  The cursor specified in an OPEN statement isalready open.   * 
* SQLSTATE=24502                                               * 
**************************************************************** 
* RECOMMENDATION:                                              * 
* Upgrade to version 9.8 fixpack 3.                            * 
****************************************************************

As a workaround, you may use the SELECT form of the FOR loop: 
create or replace procedure test 
is 
begin 
  for rec in (select COL1 from TEST1) 
  loop 
    dbms_output.put_line(rec.col1); 
  end loop; 
 
 
 
 
  for rec in (select COL1 from TEST1) 
  loop 
    dbms_output.put_line(rec.col1); 
  end loop; 
end; 
/ 
 
begin 
  test; 
end; 
/ 
 
DB20000I  The SQL command completed successfully. 
 
AAA 
BBB 
CCC 
AAA 
BBB 
CCC

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
 

This was first fixed in V98 FP3

not known / see Local fix

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

14.04.2010
10.01.2011
10.01.2011

9.8.FP3