Re: bulk collect into ... limit N
- From: Ian Cary <ian.cary@xxxxxxxxxxxxxx>
- To: tony.adolph.dba@xxxxxxxxx
- Date: Tue, 30 Jun 2009 14:52:26 +0100
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:
