Fetch out of Sequence (Out fo Control).Please help
We are facing a very strange problem related to cursors.
The error is fetch out of sequence.i.e ORA-1002
We checked all the posible causes for it, including the very common cause i.e if fetch is done after COMMIT(if cursor is based on for update),BUT can not find such problem.
The SUBroutinE flow is as following in our application
S1 is the main invoking routing.
S2 is using a cursor for update,
HOwever, it opens the cursor,fetch one row and immediately close it.
s3 -- is also a plan routing executing some select statement.
s4 -- is ALSO using a cursor for update,
HOwever, it opens the cursor,fetch one row and immediately close it. ,same as routine s2.
s5 This routine is using a plain cursor(without for update), and internally executes few sobroutines using dbms_sql package.(Say a1,a2,a3,a4,a5) dynamic procedures.
None of the a1,a2...a25 routines are using cursor for update.
Few subroutines are using plain cursor(i.e. without for update)
IN one of the internal routines that are executed at s5, there is a rollback statement. for.e. say in soubroutine a12 on specific condition.
AT THIS POINT IN TIME , WE ARE GETTING FETCH OUT OF SEQUENCE.
THE open cursors in our application are set to 400.
IS there any clue to resolve this issue.
PLEASE HELP ON THIS REGARDS.
One more point to tell is that our application works properly on oracle version 8.1.5 while it gives the above error on 8.1.7. Is it somehow related to platform??Or we are missing somethig?