RE: bulk collect into ... limit N

From: Kenneth Naim <kennaim_at_gmail.com>
Date: Wed, 1 Jul 2009 10:25:41 -0400
Message-ID: <006601c9fa57$d105d950$73118bf0$_at_com>

You can rely on %NOTFOUND at the end of the loop as it will work 100% of the time, but I agree that it should be used. I've attended several of Steven Feurstein presentations where he explains that since %NOTFOUND was/is typically used at the beginning of the a standard (non bulk collect limit n) loop it is an easy mistake to make. If it is put at the beginning some of the data will not be processed. I also agree the %count reads better.  

Ken  

From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Tony Adolph
Sent: Wednesday, July 01, 2009 1:22 AM
To: Herald ten Dam
Cc: oracle-l_at_freelists.org
Subject: Re: bulk collect into ... limit N  

The article by Steven Feuerstein (thanks Denis and Herald) says don't rely on %NOTFOUND when using bulk collect :

When you are using BULK COLLECT and collections to fetch data from your cursor, you should never rely on the cursor attributes to decide whether to terminate your loop and data processing.  

It doesn't say why it works at the end of the loop though, but I'll leave it there. Just use %count on the collection instead. It reads better any way I reckon.

Thanks for all the feedback,
Cheers
Tony

--
http://www.freelists.org/webpage/oracle-l

Received on Wed Jul 01 2009 - 09:25:41 CDT