bulk collect into ... limit N

From: Tony Adolph <tony.adolph.dba_at_gmail.com>
Date: Tue, 30 Jun 2009 11:28:38 +1200
Message-ID: <4a38d9060906291628y5087cbd0pffc0d148f3f25da6_at_mail.gmail.com>

Hi All,

I've just come across a potential "gotcha" using bulk collect into. If the bulk collect didn't "fill" up to limit, then <cursor>%notfound is set. I expected to be able to loop through a cursor in chunks until all records had been processed, but I lost the last chunk

Here's an example of how not to do it

create table t1 as select * from user_objects o where 1 = 2;

create or replace procedure p1

     n number := 7;

     type t1_rows_type is table of t1%rowtype;
     t1_rows t1_rows_type := t1_rows_type();
     cursor c1 is select * from user_objects;
     open c1;
         fetch c1 bulk collect into t1_rows limit n;
         exit when c1%notfound;
         -- exit when t1_rows.count = 0;

         forall i in 1 .. t1_rows.count
                insert into t1 values t1_rows(i);
         dbms_output.put_line(sql%rowcount||' row(s) inserted');
     end loop;
     if c1%isopen then
        dbms_output.put_line('c1 closed');
        close c1;
     end if;


SQL> select count(*) from user_objects;



SQL> exec p1;
7 row(s) inserted
7 row(s) inserted
c1 closed

Ohps,... 3 rows missing

Simple fix, but didn't expect to have to do this.


         exit when t1_rows.count = 0;
Instead of

         exit when c1%notfound;

Anyone else been caught out by this? Is there a better exit clause to use?


Env: and

Tested on and

Received on Mon Jun 29 2009 - 18:28:38 CDT

Original text of this message