Re: Implicit vs. explicit cursors (was: Re: date input)

"Matt B." <mcb_at_fightspam.sd.znet.com> wrote in message news:spb93qu7n4t138_at_corp.supernews.com...
> "Sybrand Bakker" <postbus_at_sybrandb.demon.nl> wrote in message
> news:966016226.10684.1.pluto.d4ee154e_at_news.demon.nl...
> > No not really. I would use
> > select to_date(... etc
> > into varconv
> > from dual;
> >
> > probably, but essentially that's the same.
>
> Sybrand,
>
> I have a question for you, as you seem to know a lot about this sort of
 thing
> (moving a little off-the-original-topic here).
>
> I noticed you suggested using "select into..." What's the deal with
 implicit
> (select into...) vs. explicit (where you declare them and do an
> open/fetch/close or a for-loop) cursors?
>
> I do understand that the "select into" implicit cursor expects one and
 only one
> row to be returned, so it would be useless in a loop situation. But even
 when
> I want one and only one row, the past two companies I've worked for have
 had
> departmental coding standards that said to *not* use implicit cursors.
 Are
> there disadvantages to them?
>
> Personally I think that as long as one handles the TOO_MANY_ROWS and
> NO_DATA_FOUND exceptions that might result from an implicit select-into
 cursor,
> they're perfectly OK to use. But I've heard that explicit (declared)
 cursors
> are faster because they are precompiled (?) whereas implicit ones aren't?
 (Not
> sure I understand that.)
>
> I personally like implicit cursors because I figure that Oracle is
 handling the
> opening/closing of them whereas if I declare a cursor and do an open/fetch
 and
> something goes wrong (and if I fail to trap an "OTHERS" exception that
 would
> result and close the cursor manually using the %ISOPEN attribute), the
 cursor
> might be left open unintentionally...?
>
> Thoughts...? Just curious if there are truly disadvantages to using
 "select
> into..." implicit cursors vs. declaring them as explicit ones and doing an
> open/fetch/close.
>
> Thanks,
>
> Matt
 

>
>

Depends on how you use your explicit cursors.

First a few facts.
How do you think an implicit cursor checks for the TOO_MANY_ROWS exception? Right...
By executing 2! fetches. So in all cases (lookup tables) you know the cursor is going to return exactly 1 row or no row at all, you're better off with an explicit cursor.
Secondly, most people I know using implicit cursors, usually don't trap any exception and you could argue whether embedding all select into's in their own begin end block is desirable programming style. With respect to the difference between explicit and implicit cursors. AFAIK whenever a local implicit cursor is created (so within a procedure or function) you are getting a new cursor. For explicit cursors this doesn't seem to happen. This means implicit cursors are thrashed right away and explicit cursors are kept. I have been stating this before and at that time I was corrected, but I didn't investigate this issue further yet. What I do know is an explicit cursor is to be preferred in large scale development. Suppose I have a lookup table which is used everywhere, and from which I always fetch one row. An implicit cursor would have done the job.
However, if I turn this in an explicit cursor defined in a package, I can reference it everywhere, I can make substantial savings on the number of cursors used and the number of (soft) parses. This is why I for lookup tables use packaged explicit cursors as much as possible (and of course also to centralize them)

Hth,

Sybrand Bakker, Oracle DBA Received on Sun Aug 13 2000 - 00:00:00 CEST