Re: bulk collect into ... limit N
- From: Tanel Poder <tanel@xxxxxxxxxx>
- To: tony.adolph.dba@xxxxxxxxx
- Date: Thu, 2 Jul 2009 01:31:23 +0300
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@xxxxxxxxx>wrote:
> 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
Other related posts:
