CODEWITHSUNDEEP
sqloracle-databaseplsql
Prema kumari
Prema kumari

Reputation: 171

Fetch Out of sequence <ora-01002> Rollback

Today i encountered "FETCH OUT OF SEQUENCE" ORA-01002. I have done a lot of research and i came to understand that it is quite common when we either do .

  1. Try to fetch when we have already closed the cursor.
  2. For update and commit.

My requirement is to commit every 500 records if processed successfully and in case of any issues rollback the fetched 500 records.

I have not done either of them. I have also found out that the fetch out of sequence has occured due to ROLLBACK;

I have also narrowed down to when this actually occurs. This happens only when rollback happens for the first set of records. 

   loop
    Fetch c1 bulk collect into type1 limit 500;
    exit when type1.count=0;
    forall in i..type1.count save exceptions 
    insert into the table.
    do something.....
    the computation goes on;
    Commit;
    exception when others then 
    for i in 1..sql%bulk_exceptions loop 
    Do somthing...
    end loop;
    rollback; => Fetch out of seq happens here...
   end loop;

Fetch out of sequence is happening only if the first 500 records get failed and when i give rollback command. It does'nt give ora-01002 when first 500 records gets commited and for the next 500 records fetched is rollbacked.

Any reasons why it is acting this way. Please suggest if there is any way not to avoid the ora-01002 error.

Sorry couldnt post the codings here due to company policies. But the pseudo code given above is how the coding is.

Additional information-> If i do the program this way then I am not receiving the error "ORA-01002".

loop
Fetch c1 bulk collect into type1 limit 500;
exit when type1.count=0;
COMMIT; => IF I ADD COMMIT HERE ORA-01002 doesnt appear. 
forall in i..type1.count save exceptions 
insert into the table.
do something.....
the computation goes on;
Commit;
exception when others then 
for i in 1..sql%bulk_exceptions loop 
Do somthing...
end loop;
rollback; => Fetch out of seq happens here...

end loop;

Upvotes: 2

Views: 3928

Answers (1)

J. Chomel
J. Chomel

Reputation: 8395

When you handle your records that way, you should not have a commit / rollback like this in the middle of your processing. The Rollback invalidated current cursor. You should commit after your 500 records in any case, and handle exceptions.

It is not possible for Oracle to recall where its cursor should be once you've rollbacked.

Like in statement 7 of this example, you should handle unitary exceptions of the FORALL in case

--- declare exception
failure_in_forall   EXCEPTION;  
PRAGMA EXCEPTION_INIT (failure_in_forall, -24381);  

...
-- handle it
EXCEPTION   
        WHEN failure_in_forall 
  ...

Then your good records will be processed and committed, and the bad ones to be reviewed and processed later

Upvotes: 2

Related Questions