Java Database Connectivity (JDBC)

According to the documentation of the Oracle JDBC driver, when executing batch statements which causes an error, the BatchUpdateException will return an array of int containing n integers for the n first successful executions of the batch. However, it seems that if the batch triggers an ORA-12899 (Value too large for column), the update count is inaccurate.

Below sample code demonstrates the problem:

import java.sql.*;
import java.util.Arrays;
import java.util.List;
import java.util.Properties;

public class MyApp {

private static void insertRecords(Connection connection, List<Object[]> records) throws SQLException {
connection.setAutoCommit(false);
try (PreparedStatement ps = connection.prepareStatement("insert into my_table(ID, NAME) VALUES (?, ?)")) {
for (int i = 0; i < records.size(); i++) {
Object[] r = records.get(i);
int id = (int) r[0];
String name = (String) r[1];
ps.setInt(1, id);
ps.setString(2, name);
ps.addBatch();
}
ps.executeBatch();
System.out.println("Succeeded");
connection.commit();
} catch (BatchUpdateException e) {
System.out.println("Batch exception (" + e.getErrorCode() + ") update counts: " + Arrays.toString(e.getLargeUpdateCounts()));
connection.rollback();
}
}

public static void main(String[] args) throws SQLException {
Properties props = new Properties();
props.put("user", "scott");
props.put("password", "tiger");
props.put("url", "jdbc:oracle:thin:@localhost:1521:orcl");
try (Connection connection = DriverManager.getConnection(props.getProperty("url"), props);) {
try (Statement stmt = connection.createStatement()) {
stmt.execute("drop table my_table");
} catch (SQLException e) {
// Ignoring
}
try (Statement stmt = connection.createStatement()) {
stmt.execute("create table my_table(ID NUMBER, NAME VARCHAR2(10), PRIMARY KEY(ID))");
}
insertRecords(connection, List.<Object[]>of(new Object[] { 1, "AliceOK" }));
insertRecords(connection, List.<Object[]>of(
new Object[] { 1, "Alice" },
new Object[] { 2, "FFFFFFFFFFFFFFFFFFFFFFFFFFFF" } // Record too long
));
insertRecords(connection, List.<Object[]>of(new Object[] { 1, "Alice" }));
insertRecords(connection, List.<Object[]>of(
new Object[] { 1, "Alice" },
new Object[] { 2, "Bob" }));
}
}

}

• It creates a simple table and then inserts a record with an ID=1
• It then tries to insert 2 records in a single batch, with the first one violating the primary key constraint and the second one trying to insert a value too large for the NAME column
• The update count array contained in the BatchUpdateException contains 1 integer, while it shouldn't contain any since the first record is invalid
• The next inserts behave as expected and both throw a BatchUpdateException return an empty array for the update counts.

This has been tested with the following version of the Oracle driver:

    `<dependency>`  
       `<groupId>com.oracle.database.jdbc</groupId>`  
       `<artifactId>ojdbc17</artifactId>`  
       `<version>23.7.0.25.01</version>`  
   `</dependency>`  

running on a JDK21 against an Oracle database EE 19.3.0.0.0