ORA-01002: Fetch out of Sequence caused by for loop and update statement

Question

I had a procedure which currently look like this in Oracle Database 19C

Declare
 i integer;
BEGIN
  Delete from t_invent;
  -- If I add commit here or remove the delete statement here then the error wouldnt occurred.

  FOR r_in IN (SELECT * FROM t_in) LOOP
  
    -- Insert into another table
    INSERT INTO t_invent (item_id, item_name) VALUES (r_in.item_id, r_in.item_name);
    
    -- Rollback the transaction
    if r_in.count = 90 then
       ROLLBACK; 
       -- Rollback here would cauase the procedure show this error ORA-01002: Fetch out of Sequence.
    end if;
    
  END LOOP;

END;

Based on my further testig I found that if I add Commit before the for loop the rollback below the the fetch out of sequence issue would not show in this case.

On the other hand, I had add dbms_output.put_line to trace on this issue and I found that the rollback wouldnt directly let the whole for loop crash and sometimes it can continue loop around 12 data then it will crashed.

Based on Oracle Document for this error - ORA-01002:Fetch out of Sequence. https://docs.oracle.com/en/error-help/db/ora-01002/?r=19c It show that cursor invalid would let Oracle show this error.

Besides that adding commit in the for loop like around 50 data then commit would resolve this issue but if I increase to 100 data then the error would show again.

It seen like rollback and commit would impact on the cursor.

I suspect that the rollback in the for loop will rollback the transaction and also the above delete statement. However I cannot why the cursor invalid as I didnt do any DML on the this table (t_in)

Did anyone had faced this issue before and anyone have any idea on this behaviour?

Answer 1

Rather than trying to roll back within the loop, you can throw an exception and roll back when you catch it:

DECLARE
  needs_rollback EXCEPTION;
  PRAGMA EXCEPTION_INIT(needs_rollback, -20001);
BEGIN
  Delete from t_invent;

  FOR r_in IN (SELECT * FROM t_in) LOOP
  
    -- Insert into another table
    INSERT INTO t_invent (item_id, item_name) VALUES (r_in.item_id, r_in.item_name);
    
    -- Rollback the transaction
    if r_in.count = 90 then
      --ROLLBACK; 
      raise needs_rollback;
    end if;
    
  END LOOP;

EXCEPTION
  WHEN needs_rollback THEN
    rollback;
END;

Or if you don't want that extra code, you can avoid the error by exiting the loop as soon as you've rolled back:

    -- Rollback the transaction
    if r_in.count = 90 then
      ROLLBACK; 
      EXIT;
    end if;

... but I think the exception throw/catch is a bit clearer.

fiddle showing your original code failing and these succeeding.

Notice that in your code it carries on inserting after the rollback, which I think you'd spotted from your own debugging; the exception being thrown then causes those extra inserts to also be rolled back. It doesn't process all of the available rows; in this demo there are 200 but it errors after 100, which presumably is related to something like the fetch size being used.

Either way, you can check the count value before inserting, to save it bothering when you are able to already tell you want to abort. (It's also generally not a good idea to use keywords and function names like count as column names or other identifiers - it's confusing a best.)