The content of the return value of executeBatch is different between 11g and 12c

Execution environment

The main software versions used in writing this article are as follows. Docker and Oracle Official Docker Image are used to build the Oracle Database.

software version, edition
Oracle Database 11g Oracle Database 11g Express Edition Release 11.2.0.2.0 - 64bit Production
Oracle Database 12c Oracle Database 12c Standard Edition Release 12.2.0.1.0 - 64bit Production
ojdbc6.jar (11g JDBC Driver) Oracle 11.2.0.2.0 JDBC 4.0 compiled with JDK6 on Sat_Aug_14_12:18:34_PDT_2010
ojdbc8.jar (12c JDBC Driver) Oracle 12.2.0.1.0 JDBC 4.2 compiled with javac 1.8.0_91 on Tue_Dec_13_06:08:31_PST_2016
javac javac 11.0.4
java openjdk version "11.0.4" 2019-07-16

Event summary

When upgrading the database of Java application from Oracle Database 11c to Oracle Database 12g, the content of the return value (int type array) of PreparedStatement :: executeBatch was slightly different, and the time was infinitely melted, so leave a note. I will leave it.

The source code below uses batch update to insert 3 data into table ʻUSERS`. All connection information to the database will be received as run-time arguments.

Main.java


import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.Arrays;

public class Main {
    public static void main(String[] args) {
        String url = args[0];
        String user = args[1];
        String password = args[2];

        try (Connection c = DriverManager.getConnection(url, user, password);
             PreparedStatement ps = c.prepareStatement("INSERT INTO USERS (ID, NAME) VALUES(?, ?)")) {
            ps.setInt(1, 1);
            ps.setString(2, "Alice");
            ps.addBatch();

            ps.setInt(1, 2);
            ps.setString(2, "Bob");
            ps.addBatch();

            ps.setInt(1, 3);
            ps.setString(2, "Carol");
            ps.addBatch();

            int[] updateCounts = ps.executeBatch();
            System.out.println(Arrays.toString(updateCounts));
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

After compiling this code, first execute it with the connection destination directed to 11g, then change the connection destination to 12c and execute it as follows.

$ java -cp ../lib/ojdbc6.jar:. Main jdbc:oracle:thin:@192.168.99.100:1511:xe user1 password
[-2, -2, -2]
$ java -cp ../lib/ojdbc8.jar:. Main jdbc:oracle:thin:@192.168.99.100:1512/ORCLPDB1 user1 password
[1, 1, 1]

It was confirmed that the contents of ʻupdateCounts, which is the return value of PreparedStatement :: executeBatch`, are different between 11g and 12c.

For 12c, the content is clear, and the number of updates is stored in an int type array. On the other hand, 11g ʻexecuteBatch returns an array containingStatement.SUCCESS_NO_INFO`.

Conclusion: Read the docs properly

In fact, this difference in behavior is well documented in the Oracle Database documentation.

First of all, 11g document:

If the statement batch is processed successfully, the integer array returned by the executeBatch call of the statement, that is, the update count array, always contains one element for each batch operation. In the Oracle implementation of standard batch updates, the values of the array elements are as follows: For batches of precompiled SQL statements, the number of database rows affected by the individual statements contained in the batch is unknown. Therefore, all array element values are -2. According to the JDBC 2.0 specification, a value of -2 indicates that the operation was successful, but the number of lines affected is unknown.

On the other hand, the 12c document clearly states that the behavior is different from 11g,

Starting with Oracle Database 12c Release 1 (12.1), the executeBatch method has been improved to return an int array the same size as the number of records in the batch. Each item in the return array is the number of rows in the database table affected by the corresponding record in the batch.

By the way, I haven't actually tried it, but Documentation of Oracle Database 18c Has exactly the same wording as that of 12c above. In other words, it seems that there is no difference in the behavior of PreparedStatement :: executeBatch between 12c and 18c.

Recommended Posts

The content of the return value of executeBatch is different between 11g and 12c
Behavior is different between new and clear () of ArrayList
An application that acquires the value of the accelerometer by UDP communication between C # and Android
Differences between Java, C # and JavaScript (how to determine the degree of obesity)
What is the difference between SimpleDateFormat and DateTimeFormatter? ??
Summarize the differences between C # and Java writing
[Ruby] Difference between puts and return, output and return value
Is there a performance difference between Oracle JDK and OpenJDK at the end of 2017?
Pass arguments to the method and receive the result of the operation as a return value
What is the difference between a class and a struct? ?? ??
The comparison of enums is ==, and equals is good [Java]
What is the difference between System Spec and Feature Spec?
Java language from the perspective of Kotlin and C #
[Rails] What is the difference between redirect and render?
[JAVA] What is the difference between interface and abstract? ?? ??
Verification of the relationship between Docker images and containers
What is the difference between skip and pending? [RSpec]
What is the difference between Java EE and Jakarta EE?
How is the next value of the Time object correct?
Branch processing by the return value of RestTemplate and the status code of ResponseEntity in Spring Boot
What if the results of sum and inject (: +) are different?
[Rails] What is the difference between bundle install and bundle update?
Determine that the value is a multiple of 〇 in Ruby
[Ruby] About the difference between 2 dots and 3 dots of range object.
What is the difference between an action and an instance method?
[Swift] Get the timing when the value of textField is changed
9 Corresponds to the return value
Why the width of the full screen element is 100% and the height is 100vh
Android --Is the order of serial processing and parallel processing of AsyncTask guaranteed? ??
The design concept of Java's Date and Time API is interesting
[Rails] Talk about paying attention to the return value of where
What is the difference between a web server and an application server?
[Rails] Read the RSS of the site and return the contents to the front
[Java] What is the difference between form, entity and dto? [Bean]
[Swift] The color of the Navigation Bar is different (lighter) from the specified color.
[Java] You might be happy if the return value of a method that returns null is Optional <>
The difference between puts and print in Ruby is not just the presence or absence of line breaks