Re: bulk collect into ... limit N

Hi Ian & Alex,

Although the exit when c1%notfound; at the end of the loop works... I can't
get my head around why it does. See questions as comments in line....

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;

         /*
         why is c1%notfound = TRUE here when there are still rows
         that haven't been fetched.
         Each of the 1st 2 passes 7 rows are fetched, leaving 3 rows.
         On the 3rd pass 3 rows are fetched, but %notfound
         is set true (here).. *but* it still FALSE at the end
         of the loop.  The cursor hasn't been re-fetched so
         whats happening?
         I dont understand the mechanism here.
         */
         -- 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;
         -- (from Alex)
         exit when c1%notfound;
         -- from Ian (and also works, thanks)
         -- exit when t1_rows.count < n;
     end loop;
     if c1%isopen then
        dbms_output.put_line('c1 closed');
        close c1;
     end if;
end;

SQL> exec p1;

7 row(s) inserted
7 row(s) inserted
3 row(s) inserted
c1 closed

Other related posts: