Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Trigger error (implicit vs explicit cursors)

Re: Trigger error (implicit vs explicit cursors)

From: mcstock <mcstockx_at_xenquery.com>
Date: Mon, 3 Nov 2003 07:07:47 -0500
Message-ID: <qaGdnVutJISj2DuiRVn-sQ@comcast.com>


daniel,

read my post more carefully -- except for your comments about CAST(MULTISET it seems you're disagreeing for the sake of disagreeing

"Daniel Morgan" <damorgan_at_x.washington.edu> wrote in message news:1067844690.383735_at_yasure...
> mcstock wrote:
>
> ><snipped>
> >
> >an possible alternative to an explicit cursor when the TOO_MANY_ROWS must
be
> >handled (i.e., >1 row returned when only the first row is required) would
be
> >to code a 'non-looping cursor loop' -- i.e., put the implicit cursor in a
> >FOR loop and exit immediately after saving the fetched value(s) into a
> >variable(s0 -- this performs comparably to the explicit cursor for all 3
> >cases (0, 1, >1 rows) but may be considered bad programming style by some
> >
> >
> Why bother with the loop at all?
>
> OPEN cursor
> FETCH
> CLOSE cursor
>
> >regarding the arrticle's suggestions, i respectuflly disagree with the
> >assertion that implicit cursors are always better and always easier to
code
> >and debug. i also disagree some of the reasoning and examples used in the
> >article. since the article is over two years old, i'm not posting to the
> >article itself, but since it was included as a refrence on this thread,
i'll
> >include my comment here:
> >
> >[_] the observation in the article that the statement 'close c' is hard
to
> >figure out when reviewing code is not a problem with explicit cursors,
but
> >with poor naming conventions
> >
> On this one I'll agree. I rarely find value in arguments that things are
> hard to figure out. I keep wanting
> to say "only if you don't know what you are doing." Though I'd certainly
> not say that to Tom. ;-)
>
> >[_] avoiding open/fetch/close because of the possiblility of forgetting
to
> >close the cursor is like avoiding PL/SQL blocks because of the
possibility
> >of forgetting to code an exception handler -- these things are handled by
> >decent standards and QA
> >
> Same comment as above.
>
> >[_] use explicitly declared cursors in all but very trivial cases (which
> >often become non-trivial before too long)
> >
> Can't agree. As you acknowledged above. There is a performance hit. And
> however small it is
> real and should not be ignored. I'd rather have a developer spend and
> extra hour writing something
> once than have a hit on my system for the next 10 years.
>
> >[_] only use implicit cursors (SELECT INTO) for single row-selects do not
> >need to handle the TOO_MANY_ROWS exception
> >
> I'll agree except in the case of CAST(MULTISET( ... at least
> tentatively. I haven't time to fully
> consider this.
>
> >[_] use implicit cursor processing (i.e. for loops vs the explicit
> >open/fetch/close) when multi-row result sets are expected
> >
> You are mixing metaphors. If you are refering to SELECT INTO you have no
> choice. If you are
> referring to CAST(MULTISET( you are dead wrong. And if you are referring
> to something that
> could be accomplished with an IMPLICIT CURSOR I disagree further. Once
> again you are
> ignoring preformance and scalability issues and they are not trivial.
>
> >[_] depending on your coding style, consider a non-looping FOR loop
> >(immediate exit after setting a variable) for single row selects where
there
> >is a possibility of a discardable 2nd row. if that seems like a bad
> >construct (due to coding style preferences) go with the explicit
> >open/fetch/close to avoid the overhead of handling the TOO_MANY_ROWS
> >exception
> >
> As I pointed out above, why write a loop that doesn't loop? Serves no
> useful purpose except
> to waste ticks on the clock.
>
> --
> Daniel Morgan
> http://www.outreach.washington.edu/ext/certificates/oad/oad_crs.asp
> http://www.outreach.washington.edu/ext/certificates/aoa/aoa_crs.asp
> damorgan_at_x.washington.edu
> (replace 'x' with a 'u' to reply)
>
Received on Mon Nov 03 2003 - 06:07:47 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US