Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.misc -> Re: BULK COLLECT and QUERY REWRITE
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