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

Last updated on JUNE 22, 2021

Applies to:

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 <TABLE_NAME>
(
<COLUMN1> NUMBER(15),
<COLUMN2> 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:

<COLUMN1> <COLUMN2>
--------------- -----------
5 5
4 5
3 5
2 5
1 5
0 5

expected:

<COLUMN1> <COLUMN2>
--------------- -----------
5 5
4 4
3 3
2 2
1 1
0 0

Changes

Cause

My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.