PL/SQL (MOSC)

MOSC Banner

ORA-01555 snapshot too old with a simple bulk collect/forall block

edited Jul 19, 2016 5:06AM in PL/SQL (MOSC) 8 commentsAnswered

Hi,

I'm trying to figure out why I'm getting an ORA-01555 snapshot too old error with the following piece of code:

declare
       cursor cur is
       SELECT ROWID FROM S_STG_TABLE WHERE INTEGRATION_FLAG = 0 ;
       type t_rowid is table of rowid;
       a_rowid t_rowid;
       bulk_limit number := 20000;
begin
     open cur;
     loop
         fetch cur bulk collect into a_rowid limit bulk_limit;
        
         forall i in 1 .. a_rowid.count
         update s_stg_table
         set INTEGRATION_FLAG = 1
         where rowid = a_rowid(i)
         ;
         
         commit;
         
         exit when cur%notfound;
     end loop;
     
     close cur;              
end;

Before you burn me at the pl/sql stake for this bulk collect heresy, I'd like to plead my innonce as it's not my code but what I was given to analyse

Howdy, Stranger!

Log In

To view full details, sign in to My Oracle Support Community.

Register

Don't have a My Oracle Support Community account? Click here to get started.

Category Leaderboard

Top contributors this month

New to My Oracle Support Community? Visit our Welcome Center

MOSC Help Center