bulk collect into ... limit N

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
is

     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;
begin
     open c1;
     loop
         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');
         commit;
     end loop;
     if c1%isopen then
        dbms_output.put_line('c1 closed');
        close c1;
     end if;

SQL> select count(*) from user_objects;

  COUNT(*)

        17

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.

Use:

         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?

Cheers
Tony

Env: 10.2.0.4.0 and 11.1.0.6.0

Tested on 10.2.0.4.0 and 11.1.0.6

--
http://www.freelists.org/webpage/oracle-l