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. †Ûiÿü0ÁúÞzX¬¶Ê+ƒün– {ú+iÉ^ Received on Tue Jun 30 2009 - 08:52:26 CDT