Troubleshoot & fix ORA-01002: fetch out of sequence

advertisements

_____________________________________________________________________________________________________________________

Error Description: PL/Sql block exist with ORA-01002: fetch out of sequence.

See example below

 1  declare

  2  cursor c1 is select * from emp1 for update;

  3  begin

  4  for i in c1 loop

  5  delete from emp1 where current of c1;

  6  commit;

  7  end loop;

  8* end;

SQL> /.

declare

*

ERROR at line 1:

ORA-01002: fetch out of sequence

ORA-06512: at line 4

After commenting the commit statement the pl/sql block executed properly.

  1  declare

  2  cursor c1 is select * from emp1 for update;

  3  begin

  4  for i in c1 loop

  5  delete from emp1 where current of c1;

  6  -- commit;

  7  end loop;

  8* end;

SQL> /

PL/SQL procedure successfully completed.

Problem description:

A commit after the fetch of cursor with for update clause will raise this error. So do not issue COMMIT within this cursor. Do not issue fetch after the last record is received. All select for update cursors are always reset (commit or rollback) in serializable mode.

All select cursors are always reset (commit or rlbk); if cursor's last (internal) savepoint is after the first update in this transaction, when having autocommit=on/true the the cursor is closed after the execute ODBC(SQLExecDirect ...) and failing in the next Fetch (SQLExtendedFetch)

Solution for ODBC & JDBC

Set either
serializable = false
or
SQL_AUTOCOMMIT = false (ODBC) / conn.setAutoCommit(false) (JDBC)

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, you can switch off autocommit by calling the setAutoCommit(false) method on your inbound ETD. If required, this can be performed in the userInitialize method of your collaboration, rather than executeBusinessRules.

_____________________________________________________________________________________________________________________

Website Stats