Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> Re: BULK COLLECT and QUERY REWRITE

Re: BULK COLLECT and QUERY REWRITE

From: chris <crsedgar_at_hotmail.com>
Date: 30 Oct 2006 05:30:27 -0800
Message-ID: <1162215027.843362.115370@m7g2000cwm.googlegroups.com>

DA Morgan wrote:
> chris wrote:
> > I understand that Oracle 10g will rewrite your CURSOR FOR LOOP
> > statements into a BULK COLLECT statement.
> >
> > I am contemplating no longer explicitly writing the BULK COLLECT from
> > now on as it reduces the number of lines of code and greatly simplifies
> > code.
> >
> > Can anyone see any serious flaws in this strategy?
> >
> > Kind Regards
> > Chris
>
> Bad idea. With bulk collect you control the LIMIT clause. 10g
> defaults to 100 which is a non-optimal compromise.
>
> And how does not using BULK COLLECT simplify the code?
>
> FETCH r INTO l_data
> is simpler than
> FETCH r BULK COLLECT INTO l_data LIMIT 1000;
>
> in what way? 21 characters?
>
> --
> Daniel A. Morgan
> University of Washington
> damorgan_at_x.washington.edu
> (replace x with u to respond)
> Puget Sound Oracle Users Group
> www.psoug.org

In my post I described a strategy using the CURSOR FOR LOOP as an alternative to the BULK COLLECT, this is very different from your example. All you have shown here is an explicit CURSOR FETCH specified to use BULK COLLECT.

The BULK COLLECT statement requires additional coding to handle returning more rows than the specified LIMIT clause. Using the FOR LOOP CURSOR removes this requirement and any explicit OPEN and CLOSE cursor therefore simplifying the code.

Regards,
Chris Received on Mon Oct 30 2006 - 07:30:27 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US