Environment: Oracle 11.2.0.3
ORA-01555 "Snapshot too old" occurs when "fetch across commits" is used (committing inside of open cursor loop).
Possible solution is to use Oracle collection instead of cursor:
set serveroutput on
declare
TYPE nested_typ IS TABLE OF NUMBER;
t1 nested_typ;
begin
select AGENT_INSURER_ID BULK COLLECT into t1
from AGENT_INSURER
where 1=1; -- conditions
--
FOR i IN t1.FIRST..t1.LAST LOOP
DBMS_OUTPUT.PUT_LINE('ROW_NUM: '||i||' AGENT_INSURER_ID: '||t1(i));
procedure1(t1(i)); -- which has COMMIT inside
END LOOP;
end;
/
ORA-01555 "Snapshot too old" occurs when "fetch across commits" is used (committing inside of open cursor loop).
Possible solution is to use Oracle collection instead of cursor:
set serveroutput on
declare
TYPE nested_typ IS TABLE OF NUMBER;
t1 nested_typ;
begin
select AGENT_INSURER_ID BULK COLLECT into t1
from AGENT_INSURER
where 1=1; -- conditions
--
FOR i IN t1.FIRST..t1.LAST LOOP
DBMS_OUTPUT.PUT_LINE('ROW_NUM: '||i||' AGENT_INSURER_ID: '||t1(i));
procedure1(t1(i)); -- which has COMMIT inside
END LOOP;
end;
/