Re: Trigger error (implicit vs explicit cursors)

mcstock wrote:
>
> i checked out the reference (thank you) and ran my own tests (variations of
> 2 anonymous pl/sql blocks with 1000 selects in a loop, each run 10 times in
> 8.1.7.0.0 with timed statistics and sql trace on)
>
> my suggestion of "consider using explicit cursors for single-row selects...
> " is only valid 1 out of 3 scenarios, where the extra fetch still occurs --
> specifically when the single row select may raise TOO_MANY_ROWS... if an
> exception handler is used, and the exception is raised and ignored, CPU time
> appears to be 50% higher than with the open/fetch/close syntax of the
> implicit cursor
>
> however, in the two other possibilities (one row returned or NO_DATA_FOUND
> exception handled and ignored) the implicit cursor has about a 1% CPU
> advantage -- which very possibly could be significant in critical
> high-volume function
>
> 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
>
> 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
> [_] 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
> [_] deeper into the the article, it actually does advocate explicit cursors,
> using ref cursors, but just not explicit cursor processing
> (open/fetch/close)
> [_] beyond initial simple examples, the amount of code needed to avoid
> open/fetch/close is often the same or more -- not always simpler to code
> [_] the emphasis on fewer lines of PL/SQL in order to have fewer lines of
> interpretted code ignores the fact that not all line of code are equal in
> processing requirements or side affects
> -- note that with Oracle9i native compilation (not available at the time of
> the article) the impact of interpretted code is pretty much a mute point
> (within the limitations of 9i native compilation) -- but we won't all be
> using 9i until 12pi is out, will we?
> [_] perhaps the most troubling suggestion -- some of the examples introduce
> a very problematic maintenance issue -- that is, requiring a SQL statement's
> SELECT list and FROM clause to be coded twice in two independent sections of
> code, just to be able to declare a record structure based on the cursor -- a
> real code maintenance trap
> [_] the article totally dismisses reusability as a minor issue -- certainly
> not so when you start getting complex business rules coded into complex
> SELECT statements -- SQL reusability can be a tremendous advantage and can
> greatly reduces code complexity and maintenance while increases code
> stability, and should not be sacrificed without well thought-out, tested,
> and documented justification
>
> looking into this further (thanks for the incentive) i would strongly
> recommend:
>
> [_] use explicitly declared cursors in all but very trivial cases (which
> often become non-trivial before too long)
> [_] only use implicit cursors (SELECT INTO) for single row-selects do not
> need to handle the TOO_MANY_ROWS exception
> [_] use implicit cursor processing (i.e. for loops vs the explicit
> open/fetch/close) when multi-row result sets are expected
> [_] 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
>
> -- mcs
>
> "Daniel Morgan" <damorgan_at_x.washington.edu> wrote in message
> news:1067719553.218467_at_yasure...
> mcstock wrote:
>
> <snipped>
>
> please supply some support for your assertion that implicit cursors are
> always preferable. less overhead? less typing? less filling? tastes great?
>
> <snipped>
>
> Since you asked:
>
> Go to http://asktom.oracle.com and search for:
> "What is the reason that implicit cursors are faster than explicit cursors"
> the link should be:
> http://asktom.oracle.com/pls/ask/f?p=4950:8:10891502989889100821::NO::F4950_P8_DISPLAYID,F4950_P8_CRITERIA:1205168148688,
>
> Be sure to paste it back together again if it wraps.
>
> I wonder if Tom's getting paid by the length of his URLs. ;-)
>
> --
> 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)

The too_many_rows is a dead issue.

If you have a cursor that may return more than 1 row and you code open/fetch/close, then you are basically seeing that you are interested in just the first. In which case, the implicit cursor can be

select .. into ...
where rownum = 1

and you'll never ever get the too_many_rows. Received on Mon Nov 03 2003 - 04:55:40 CST