Re: Implicit vs. Explicit cursors

Marc wrote:
>
> In our current project we discovered some bugs which we would never have
> discovered with explicit cursors (too_many_rows exceptions where they were
> not supposed to occur and which could not be prevented by means of unique
> constraints...).
>
> Marc
>
> Paul Martin wrote in message <8gjlia$i9s$1_at_bw107zhb.bluewin.ch>...
> >Hello
> >
> >Can anybody tell me how penalizing implicit cursor are on an Oracel 7.3.4.
> >Is it worth changing all the implicit cursors to explicit cursors in our
> >plsql packages ?
> >Should implicit cursors even be used ?

That would only occur if you use the explicit cursor in a manner that does not check for TOO_MANY_ROWS.

SELECTs should always be performed with explicit cursor. I have yet to find a single instance when an implicit cursor will work better then an implicit one and as a dba I have actually had to tell people to rewrite their code to use explicit cursors because the way they wrote their code the implicit cursor was being re-parsed with every use and I had to teach them how to write a cursor that could be reused.

On the other hand, using implicit cursors for INSERT, UPDATE and DELETE is the norm - although it is important to rigorously check for error conditions and bind variables should be used whenever possible.

Also, completely forget about OPEN/FETCH/CLOSE for explicit cursors. Always use a cursor FOR loop. Once you get used to writing them and handling all of the possible exceptions/errors you'll never want to go back to the OPEN/FETCH/CLOSE syntax. Steve Feuerstein got this wrong in his book and too many people follow his advice without thinking through all of the consequences (he claims that there are no adverse consequences and tries to lead the reader to believe that there are inherent problems with the cursor FOR loop and trusts that the reader won't know better). Received on Fri May 26 2000 - 00:00:00 CDT