Applies to:
JDBC – Version: 11.1.0.7 and later [Release: 11.1 and later ]
Information in this document applies to any platform.
Symptoms
Wrong results are obtained when running batch JDBC program with subquery against Oracle Server 11g database. Correct result are obtained when running the same program against an Oracle Server 10g.
JDBC Driver Database
—— ——–
10g 10g – works
10g 11g – fails
11g 10g - works
11g 11g – fails
Steps to reproduce:
CREATE TABLE TESTTBL
(
TESTTBL_ID NUMBER(15),
DESCRIPTION VARCHAR2(30)
)
Java Code (part where it fails):
private static void execute(Connection conn) throws SQLException {
PreparedStatement stmt = null;
try {
stmt = conn.prepareStatement(INSERT_STMT);
for (int i = NUM_ROWS-1; i >=0 ; i–) {
stmt.setInt(1, i);
stmt.setString(2, “Second-” + i);
stmt.addBatch();
}
stmt.executeBatch();
conn.commit();
}
finally {
close(stmt);
}
}
Output:
TESTTBL_ID DESCRIPTION
————— ———–
5 5
4 5
3 5
2 5
1 5
0 5
expected:
TESTTBL_ID DESCRIPTION
————— ———–
5 5
4 4
3 3
2 2
1 1
0 0
Cause
This issue is not caused by the JDBC driver, but an RDBMS issue.
This issue is reported in Bug.9681380 DIFFERENT RESULTS WITH 10.2 AND 11.1 DATABASE.
The bug was closed as ‘Not a bug” as it was determined this is not a JDBC issue.
The issue occurs due to unpublished RDBMS bug:7000281 Difference in FORALL statement behaviour in 11g.
Solution
=== ODM Solution / Action Plan ===
Download patch:7000281 – DIFFERENCE IN FORALL STATEMENT BEHAVIOUR IN 11G, and apply it on top of database 11.1.0.7.
OR
Upgrade to RDBMS 11.2.
References
NOTE:851982.1 – Difference in FORALL statement behaviour in 11G
© 2009, www.oracledatabase12g.com. 版权所有.文章允许转载,但必须以链接方式注明源地址,否则追究法律责任.
相关文章 | Related posts:
- Starting With Oracle JDBC Drivers
- CLOBs and NCLOBs character set storage in Oracle Release 8i, 9i, 10g and 11g
- Wrong Results from a RANGE-LIST PARTITIONED TABLE- Bug 5882821
- COMMITTING OVER A SELECT FOR UPDATE
- Upgrade to Oracle Real Application Clusters 11g Release 2 Key Success Factors
- Database Initialization Parameters for Oracle Applications Release 12
- Oracle database 11g release2发布
- 11g: Scheduler Maintenance Tasks or Autotasks
- Oracle Database 11g Transparent Data Encryption
- Protecting Applications Using Oracle 11g Database Vault
Difference in FORALL statement behaviour in 11G
Applies to:
Oracle Server – Enterprise Edition – Version: 11.1.0.6
PL/SQL – Version: 11.1.0.6
This problem can occur on any platform.
Symptoms
Difference in behaviour of FORALL in 11g.
FORALL inserts only first row in the loop.
The below example shows the behaviour of FORALL in both 10g and 11g
When the sample is tested on Oracle version 10G
===================================
SQL> set serveroutput on size unlimited
create table t1 (id int);
create table t2 (id int);
DECLARE
TYPE idList IS VARRAY(3) OF NUMBER;
ids idList := idList(0,1,2);
BEGIN
— Batch Insert
FORALL i IN 1..3
insert into t1 select ids(i) from dual where ids(i) = (select ids(i) from dual);
– Loop Insert
for i in 1..3 loop
begin
insert into t2 select ids(i) from dual where ids(i) = (select ids(i) from dual);
DBMS_Output.Put_Line(ids(i));
end;
end loop;
END;
/
SQL>
Table created.
SQL>
Table created.
SQL>
0
1
2
PL/SQL procedure successfully completed.
SQL> select * from t1;
ID
———-
0
1
2
SQL> select * from t2;
ID
———-
0
1
2
Oracle version 11.1
==============
SQL> set serveroutput on size unlimited
create table t1 (id int);
create table t2 (id int);
SQL>
Table created.
SQL>
Table created.
SQL> DECLARE
2 TYPE idList IS VARRAY(3) OF NUMBER;
3 ids idList := idList(0,1,2);
4 BEGIN
5 — Batch Insert
6 FORALL i IN 1..3
7 insert into t1 select ids(i) from dual where ids(i) = (select ids(i) from dual);
8 — Loop Insert
9 for i in 1..3 loop
10 begin
11 insert into t2 select ids(i) from dual where ids(i) = (select ids(i) from dual);
12 DBMS_Output.Put_Line(ids(i));
13 end;
14 end loop;
15 END;
16 /
SQL>
0
1
2
PL/SQL procedure successfully completed.
SQL> select * from t1;
ID
———-
0
SQL> select * from t2;
ID
———-
0
1
2
As you can see above , when the same sample code is run on 11.1.0, only one row is inserted in table t1.
But ideally three rows should be inserted.
Cause
The cause of this problem has been identified in unpublished Bug 7000281
Solution
1) This issue has been fixed in Oracle version 11.2.0
2) To implement the solution on 11.1.0, you can apply the Patch.7000281.