| Latest versions | fixlist |
| 11.1.0.7 |
 |
| 10.5.0.9 |
|
| 10.1.0.6 |
|
| 9.8.0.5 |
|
| 9.7.0.11 |
|
| 9.5.0.10 |
|
| 9.1.0.12 |
|
| Have problems? - contact us. | |
| Register for free |
|
| Contact form |
|
DB2 - Problem description
| Problem IC76989 | Status: Closed |
SQL%ROWCOUNT not updated after a PL/SQL EXECUTE IMMEDIATE statement | |
| product: | |
DB2 FOR LUW / DB2FORLUW / 970 - DB2 | |
| Problem description: | |
In PL/SQL code, the default cursor attribute SQL%ROWCOUNT
may not be updated after an EXECUTE IMMEDIATE statement.
If your EXECUTE IMMEDIATE statement performs an UPDATE,
DELETE, INSERT, or SELECT INTO, SQL%ROWCOUNT will not be
updated unless your EXECUTE IMMEDIATE statement contains
a BULK COLLECT clause.
This can be demonstrated using the following test case:
----
connect to test;
set sqlcompat plsql;
drop table T1;
drop table T2;
create table T1(C1 integer);
insert into T1 values
2, 3, 5, 7, 11, 13, 17, 19, 23;
create table T2(C1 integer);
create or replace procedure test(r1 out bigint, r2 out bigint)
as
begin
insert into T2(C1)
select C1 from T1 where C1 < 6; -- 3 rows
r1 := SQL%ROWCOUNT;
execute immediate
'insert into T2(C1) select C1 from T1 where C1 < 3'; -- 1
row
r2 := SQL%ROWCOUNT;
end;
/
call test(?, ?);
connect reset;
terminate;
----
call test(?, ?)
Value of output parameters
--------------------------
Parameter Name : R1
Parameter Value : 3
Parameter Name : R2
Parameter Value : 3
Return Status = 0
The expected result in this case should be R1=3, but
R2=1. | |
| Problem Summary: | |
**************************************************************** * USERS AFFECTED: * * none * **************************************************************** * PROBLEM DESCRIPTION: * * SQL%ROWCOUNT is not updated after a PL/SQL EXECUTE * * IMMEDIATE statement; please see the Error Description * * for more details. * **************************************************************** * RECOMMENDATION: * * Upgrade to DB2 Version 9.7 Fix Pack 5. * **************************************************************** | |
| Local Fix: | |
| available fix packs: | |
DB2 Version 9.7 Fix Pack 5 for Linux, UNIX, and Windows | |
| Solution | |
This problem is fixed in DB2 Version 9.7 Fix Pack 5. | |
| Workaround | |
not known / see Local fix | |
| Timestamps | |
Date - problem reported : Date - problem closed : Date - last modified : | 15.06.2011 27.02.2012 27.02.2012 |
| Problem solved at the following versions (IBM BugInfos) | |
9.7.FP5 | |
| Problem solved according to the fixlist(s) of the following version(s) | |
| 9.7.0.5 |
|
