Re: Trigger error (implicit vs explicit cursors)

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)