|
Problem IC67681
|
Status: Closed |
SQL0502N(CURSOR ALREADY OPEN) WHEN TWO FOR LOOPS USE THE SAME CURSOR IN THE SAME PL/SQL ROUTINE |
| product: |
DB2 FOR LUW / DB2FORLUW / 970 - DB2 |
| Problem description: |
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
During SQL processing it returned:
SQL0502N The cursor specified in an OPEN statement is already
open. SQLSTATE=24502 |
| Problem Summary: |
****************************************************************
* USERS AFFECTED: *
* ALL *
****************************************************************
* PROBLEM DESCRIPTION: *
* 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 *
****************************************************************
* RECOMMENDATION: *
* Upgrade to Version 9.7 Fix Pack 2. *
**************************************************************** |
| Local Fix: |
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 |
| available fix packs: |
DB2 Version 9.7 Fix Pack 2 for Linux, UNIX, and Windows
DB2 Version 9.7 Fix Pack 3 for Linux, UNIX, and Windows
DB2 Version 9.7 Fix Pack 3a for Linux, UNIX, and Windows
DB2 Version 9.7 Fix Pack 4 for Linux, UNIX, and Windows
DB2 Version 9.7 Fix Pack 5 for Linux, UNIX, and Windows
DB2 Version 9.7 Fix Pack 6 for Linux, UNIX, and Windows
DB2 Version 9.7 Fix Pack 7 for Linux, UNIX, and Windows
DB2 Version 9.7 Fix Pack 9a for Linux, UNIX, and Windows
DB2 Version 9.7 Fix Pack 8 for Linux, UNIX, and Windows
DB2 Version 9.7 Fix Pack 9 for Linux, UNIX, and Windows
DB2 Version 9.7 Fix Pack 10 for Linux, UNIX, and Windows
|
| Solution |
This was first fixed by Version 9.7 FP2. |
| Workaround |
There are two workarounds.
1. Close cursor before the second loop
2. See LOCAL FIX |
| BUG-Tracking |
forerunner : APAR is sysrouted TO one or more of the following: IC67895
follow-up : |
| Timestamps |
Date - problem reported :
Date - problem closed :
Date - last modified :
| 06.04.2010
25.05.2010
20.12.2010 |
| Problem solved at the following versions (IBM BugInfos) |
9.7.FP2
|
| Problem solved according to the fixlist(s) of the following version(s) |
| 9.7.0.2
|
 |
