ORA-01002: fetch out of sequence in ATG BCC

We had an issue during a deployment in which an ORA-01002 was thrown. The interesting part is when we compile and run what is below…

import java.sql.*;

public class test {
  public static void main (String args[]) throws Exception {
    Class.forName("oracle.jdbc.driver.OracleDriver");
    Connection conn = DriverManager.getConnection("jdbc:oracle:thin:test/test@host:1521/service");
    Statement stm = conn.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE);
    //Statement stm = conn.createStatement();
    System.out.println(stm.getFetchSize());
    stm.setFetchSize(Integer.parseInt(args[0]));
    stm.execute("begin dbms_monitor.session_trace_enable(waits=>true,binds=>true); end;");
    ResultSet rst = stm.executeQuery("select c from test for update");
    while (rst.next()) {
      System.out.println(rst.getInt(1));
      conn.commit();
    }
  }
}

…we see the following when we fetch less than the number of rows in the table/query (two, in our case)…

host:oracle:sid:/u01/orahome>java test 1
10
1
Exception in thread "main" java.sql.SQLException: ORA-01002: fetch out of sequence

        at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:457)
        at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:400)
        at oracle.jdbc.driver.T4C8Oall.processError(T4C8Oall.java:926)
        at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:476)
        at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:200)
        at oracle.jdbc.driver.T4C8Oall.doOALL(T4C8Oall.java:543)
        at oracle.jdbc.driver.T4CStatement.doOall8(T4CStatement.java:197)
        at oracle.jdbc.driver.T4CStatement.fetch(T4CStatement.java:1531)
        at oracle.jdbc.driver.OracleResultSetImpl.close_or_fetch_from_next(OracleResultSetImpl.java:750)
        at oracle.jdbc.driver.OracleResultSetImpl.next(OracleResultSetImpl.java:624)
        at oracle.jdbc.driver.ScrollableResultSet.cacheRowAt(ScrollableResultSet.java:6429)
        at oracle.jdbc.driver.ScrollableResultSet.isValidRow(ScrollableResultSet.java:6342)
        at oracle.jdbc.driver.ScrollableResultSet.next(ScrollableResultSet.java:657)
        at oracle.jdbc.driver.SensitiveScrollableResultSet.next(SensitiveScrollableResultSet.java:104)
        at oracle.jdbc.driver.UpdatableResultSet.next(UpdatableResultSet.java:686)
        at test.main(test.java:13)
host:oracle:sid:/u01/orahome>

…but we don’t see the corresponding ORA-01002 error in the 10046 generated trace file. As such, we got creative and looked for all cursors that have a break/reset event attached to their CURSOR#. When we did this, we identified the culprit.

We ended up increasing the rowPrefetchSize in the corresponding JBOSS datasource xml file (google it), and the issue went away.

Our thought is the ATG code uses a transaction management bean somewhere, and when it finds more than ten rows in the resultset, it tries to fetch the next one when it has already committed within that same loop. That is a no-no. The reason this is so is because when you open an updateable resultset and commit, you no longer have “rights” to the resultset to be updateable. As long as you don’t go back to the database for more data in the resultset, you are OK. If you go back, Oracle will not allow you to pull rows from an updateable resultset without a corresponding transaction (which ended when you committed).

Increasing the fetch size is a band-aid, but resolved the issue in our case.

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.