RE: bulk collect into ... limit N

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

Hi,

Steven Feuerstein wrote it in the Oracle magazine: http://www.oracle.com/technology/oramag/oracle/08-mar/o28plsql.html. It is the way Oracle works.

Herald ten Dam
Superconsult

---

Van: oracle-l-bounce_at_freelists.org [oracle-l-bounce_at_freelists.org] namens Tony Adolph [tony.adolph.dba_at_gmail.com] Verzonden: woensdag 1 juli 2009 0:05
Aan: Ian Cary
CC: oracle-l_at_freelists.org; oracle-l-bounce_at_freelists.org 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
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

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

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