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

[Quoted] "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 [Quoted] (moving a little off-the-original-topic here).

I noticed you suggested using "select into..." What's the deal with implicit [Quoted] (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 [Quoted] row to be returned, so it would be useless in a loop situation. But even when [Quoted] I want one and only one row, the past two companies I've worked for have had [Quoted] departmental coding standards that said to *not* use implicit cursors. Are [Quoted] 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, [Quoted] they're perfectly OK to use. But I've heard that explicit (declared) cursors [Quoted] are faster because they are precompiled (?) whereas implicit ones aren't? (Not [Quoted] sure I understand that.)

I personally like implicit cursors because I figure that Oracle is handling the [Quoted] opening/closing of them whereas if I declare a cursor and do an open/fetch and [Quoted] something goes wrong (and if I fail to trap an "OTHERS" exception that would [Quoted] result and close the cursor manually using the %ISOPEN attribute), the cursor [Quoted] might be left open unintentionally...?

Thoughts...? Just curious if there are truly disadvantages to using "select [Quoted] into..." implicit cursors vs. declaring them as explicit ones and doing an open/fetch/close.

Thanks,

Matt Received on Sat Aug 12 2000 - 00:00:00 CEST