2014-01-31

Using Oracle collection instead of cursor

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;
/