Re: bulk collect into ... limit N

From: Ian Cary <ian.cary_at_ons.gsi.gov.uk>
Date: Tue, 30 Jun 2009 14:52:26 +0100
Message-ID: <OF446BEE3E.00C57749-ON802575E5.004BD473-802575E5.004C36BE_at_ons.gsi.gov.uk>



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_at_gm|
| | ail.com |
| | Sent by: |
| | oracle-l-bounce_at_fr|
| | eelists.org |
| | |
| | |
| | 30/06/2009 00:28 |
| | Please respond to |
| | tony.adolph.dba |
| | |
|---------+-----------------------------> >--------------------------------------------------------------------------------------------------------------| | | | To: oracle-l_at_freelists.org | | 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
ise t1_rows_type is table of
t1%rowtype;1_rows_type := t1_rows_type();ct * from user_objects; beginlooplimit n;     -- exit when t1_rows.count = 0; ert into t1 values t1_rows(i);rowcount||' row(s) inserted');end loop;output.put_line('c1 closed');    end if;
end;
/

SQL> select count(*) from user_objects;

  COUNT(*)
----------c 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:unt = 0;
Instead of nd;

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. i0zX+n{+i^ Received on Tue Jun 30 2009 - 08:52:26 CDT

Original text of this message