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?
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.)
0