Please see my other blog for Oracle EBusiness Suite Posts - EBMentors

Search This Blog

Note: All the posts are based on practical approach avoiding lengthy theory. All have been tested on some development servers. Please don’t test any post on production servers until you are sure.

Monday, May 21, 2012

ORA-01002: fetch out of sequence

"ORA-01002: fetch out of sequence" usually means that a SQL fetch has been issued after a prior commit has closed a server side cursor.
The current default behaviour of the Oracle JDBC driver is to autocommit SQL Statements. So it is possible that a commit has been issued which you did not expect. Eg if you are performing update statements inside the fetch loop, it is possible that an automatic commit has been issued after the update which has closed the fetch loop's cursor.
To remedy this,  Disable "Auto Commit" for the used connection before using the statement "select ... for update".
Add following line of code to your program:

  OracleDataSource ods = new OracleDataSource();
  Connection conn = ods.getConnection();
  conn.setAutoCommit(false);  // Setting AutoCommit to OFF

If you subsequently perform any updates to the rows returned by the result set, disable autocommit and do not perform any manual commits if you still have additional rows to fetch. Any commit performed while any 'for update' cursor is still open cause subsequent fetches to return the ORA-1002 error.
Ref: 1016747.102,1027196.1,257914.1,18593.1

No comments: