Re: bulk collect into ... limit N
From: Tanel Poder <tanel_at_poderc.com>
Date: Thu, 2 Jul 2009 01:31:23 +0300
Message-ID: <4602f23c0907011531t499840ech7f66d4b76e16e7cb_at_mail.gmail.com>
I haven't read the whole thread here, but the SQL%NOTFOUND condition is raised when the fetch reaches the end of data condition.
Date: Thu, 2 Jul 2009 01:31:23 +0300
Message-ID: <4602f23c0907011531t499840ech7f66d4b76e16e7cb_at_mail.gmail.com>
I haven't read the whole thread here, but the SQL%NOTFOUND condition is raised when the fetch reaches the end of data condition.
Let say you fetch 10 rows at a time using bulk collect, so if you have only 9 rows in the resultset, then the bulk collect returns 9 rows *AND* raises SQL%NOTFOUND. So, SQL%NOTFOUND rather means "end of data reached", not "no data at all"...
-- Tanel Poder http://blog.tanelpoder.com On Wed, Jul 1, 2009 at 1:05 AM, Tony Adolph <tony.adolph.dba_at_gmail.com>wrote:Received on Wed Jul 01 2009 - 17:31:23 CDT
> 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
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
-- Tanel Poder http://blog.tanelpoder.com -- http://www.freelists.org/webpage/oracle-l