Re: cursor looping - best practices?

On Fri, 15 Jun 2007 08:00:20 -0700, spacemarine_at_mailinator.com wrote:

>question,
>
>is there a best practice philosophy on looping thru a cursor of
>results in SQL (to tally things and whatnot)? im just curious if its
>considered something to be avoided, or a perfectly acceptable
>practice?
>
>
>
>thanks,
>sm

Obviously this is a version dependent question. I always failed to see why people posting here assume everyone here is a mindreader and/or keeps track of versions in use. Rest assured: we don't.

Basically in 8i there is no alternative. In 9i and higher there is no reason why shouldn't use BULK COLLECT. The rationale behind this is that every fetch (array or non-array) results in a network roundtrip, which means latency. As you even can use collections using a simple SELECT INTO, there shouldn't be any reason why you stick to using CURSOR FOR loops.

So the best practice is NOT to use CURSOR FOR loops.

--

 
Sybrand Bakker
Senior Oracle DBA