JDBC Batch Processing With Subquery in Select Clause Returns Incorrect Results With 11G Release 1 Database. (Doc ID 1201204.1)

Last updated on MARCH 08, 2017

Applies to:

JDBC - Version 11.1.0.7 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

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms