4 Replies Latest reply: Feb 8, 2012 4:18 PM by EJP RSS

    Can getGeneratedKeys() be used when executing batches of statements?

    359669

      All,

      Can you retrieve keys generated by a batch of insert statements? The following code produces an ArrayIndexOutOfBoundsException from the Oracle T4CNumberAccessor.unmarshalOneRow() method (see stack trace at the bottom of this message).

      The code is:

      String sql = "INSERT INTO FOO (ID, NAME) VALUES (FOO_SEQ.NEXTVAL, ?)";
      String generatedColumns[] = {"ID"};
      PreparedStatement pstmt = connection.prepareStatement(sql, generatedColumns);

      pstmt.setString(1, "A");
      pstmt.addBatch();
      pstmt.setString(1, "B");
      pstmt.addBatch();
      pstmt.setString(1, "C");
      pstmt.addBatch();

      //EXCEPTION OCCURS HERE
      pstmt.executeBatch();

      The exception is:

      java.lang.ArrayIndexOutOfBoundsException: 22
      at oracle.jdbc.driver.T4CNumberAccessor.unmarshalOneRow(T4CNumberAccessor.java:190)
      at oracle.jdbc.driver.T4C8Oall.receive(T4C8Oall.java:610)
      at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:216)
      at oracle.jdbc.driver.T4CPreparedStatement.executeForRows(T4CPreparedStatement.java:955)
      at oracle.jdbc.driver.OraclePreparedStatement.executeBatch(OraclePreparedStatement.java:10580)

        • 1. Re: Can getGeneratedKeys() be used when executing batches of statements?
          359669
          I should have mentioned that this example uses "standard update batching", as described in the "Oracle® Database JDBC Developer's Guide and Reference". I get the same error when using "oracle update batching". In that case the error is postponed until the number executeUpdate() calls meets the "batch value", i.e. triggers the sending of the batch.
          • 2. Re: Can getGeneratedKeys() be used when executing batches of statements?
            629660
            An update:

            I've just downloaded ojdbc6.jar, and rerun my test using
            version 11.1.0.6.0-Production of the driver. The behavior
            is the same:
            Exception in thread "main" java.lang.ArrayIndexOutOfBoundsException: 22
            at oracle.jdbc.driver.T4CNumberAccessor.unmarshalOneRow(T4CNumberAccessor.java:192)
            at oracle.jdbc.driver.T4C8Oall.receive(T4C8Oall.java:703)
            at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:194)
            at oracle.jdbc.driver.T4CPreparedStatement.executeForRows(T4CPreparedStatement.java:947)
            at oracle.jdbc.driver.OraclePreparedStatement.executeBatch(OraclePreparedStatement.java:9271)
            at oracle.jdbc.driver.OracleStatementWrapper.executeBatch(OracleStatementWrapper.java:210)
            at Test.main(Test.java:48)
            • 3. Re: Can getGeneratedKeys() be used when executing batches of statements?
              714055
              You should directly specify how big your batching is. For that reason you may use ExecutionContext where you can set setBatchingLimit(int) and setBatching(bool). This operation will tell execution context the size of supposed batching and also should be batching performed at all or not. You can add batching jobs but you can not execute the jobs without proper initialization of batching job size - it will fail with out of bounds.
              • 4. Re: Can getGeneratedKeys() be used when executing batches of statements?
                EJP
                Moderator action: Deleted numerous pointless thread revivals asking for the solution, which has been given above.

                Locking.