RE: bulk collect into ... limit N

From: Herald ten Dam <>
Date: Wed, 1 Jul 2009 06:59:58 +0200
Message-ID: <BACE06B587FB3C47AC31296417C11434016BE7075408_at_thnms004.TheHumanNetwork.local>


Steven Feuerstein wrote it in the Oracle magazine: It is the way Oracle works.

Herald ten Dam

Van: [] namens Tony Adolph [] Verzonden: woensdag 1 juli 2009 0:05
Aan: Ian Cary
CC:; Onderwerp: 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

     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;

         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;


SQL> exec p1;

7 row(s) inserted
7 row(s) inserted
3 row(s) inserted

c1 closed
Received on Tue Jun 30 2009 - 23:59:58 CDT

Original text of this message