Re: bulk collect into ... limit N

I see you got a reply to this but I didn't see what it was.

I usually use

exit when t1_rows.count < n;  -- where n is the  limit

after the forall statement.

Cheers,

Ian



|---------+----------------------------->
|         |           tony.adolph.dba@gm|
|         |           ail.com           |
|         |           Sent by:          |
|         |           oracle-l-bounce@fr|
|         |           eelists.org       |
|         |                             |
|         |                             |
|         |           30/06/2009 00:28  |
|         |           Please respond to |
|         |           tony.adolph.dba   |
|         |                             |
|---------+----------------------------->
  
>--------------------------------------------------------------------------------------------------------------|
  |                                                                             
                                 |
  |       To:       oracle-l@xxxxxxxxxxxxx                                      
                                 |
  |       cc:                                                                   
                                 |
  |       Subject:  bulk collect into ... limit N                               
                                 |
  
>--------------------------------------------------------------------------------------------------------------|




Hi All,

I've just come across a potential "gotcha" using bulk collect into.  If the
bulk collect didn't "fill" up to limit, then <cursor>%notfound is set.  I
expected to be able to loop through a cursor in chunks until all records
had been processed, but I lost the last chunk

Here's an example of how not to do it

create table t1 as select *  from user_objects o where 1 = 2;

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;         exit when c1%notfound;         -- 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;
end loop;     if c1%isopen then        dbms_output.put_line('c1
closed');        close c1;     end if;
end;
/

SQL> select count(*) from user_objects;

  COUNT(*)
----------        17

SQL> exec p1;
7 row(s) inserted
7 row(s) inserted
c1 closed

Ohps,... 3 rows missing

Simple fix, but didn't expect to have to do this.

Use:         exit when t1_rows.count = 0;
Instead of          exit when c1%notfound;

Anyone else been caught out by this?  Is there a better exit clause to use?

Cheers
Tony

Env:  10.2.0.4.0 and 11.1.0.6.0








Tested on 10.2.0.4.0 and 11.1.0.6

This email was received from the INTERNET and scanned by the Government
Secure Intranet anti-virus service supplied by Cable&Wireless in
partnership with MessageLabs. (CCTM Certificate Number 2007/11/0032.) In
case of problems, please call your organisation’s IT Helpdesk.
Communications via the GSi may be automatically logged, monitored and/or
recorded for legal purposes.

For the latest data on the economy and society consult National Statistics at 
http://www.statistics.gov.uk

*********************************************************************************


Please Note:  Incoming and outgoing email messages are routinely monitored for 
compliance with our policy on the use of electronic communications
*********************************************************************************


Legal Disclaimer  :  Any views expressed by the sender of this message are not 
necessarily those of the Office for National Statistics
*********************************************************************************


The original of this email was scanned for viruses by the Government Secure 
Intranet virus scanning service supplied by Cable&Wireless in partnership with 
MessageLabs. (CCTM Certificate Number 2007/11/0032.) On leaving the GSi this 
email was certified virus free.
Communications via the GSi may be automatically logged, monitored and/or 
recorded for legal purposes.
��i��0���zX���+��n��{�+i�^

Other related posts: