Re: Trigger error

what i'm recalling about explicit vs implicit for single row fetches is that the implicit must do 2 fetches, in order to be able to raise the TOO_MANY_ROWS (ORA-01422: exact fetch returns more than requested number of rows) exception -- does not seem like a lot of overhead, but this is they type of thing that can kill application performance if it occurs within any sort of a looping logic -- either an explicit loop, or in a row-level trigger. with an explicit trigger you explicitly open, fetch once, and close.

outside of single-row queries (the topic at hand) a very major advantage of implicit cursors is to reuse the same sql from multiple calls -- especially if you use parameterized cursors. for example, we are in the midst of a implementing a medical billing application -- the claims generator and the claims previewer share the same parameterized cursor, so the logic is coded once, with one process using the cursor to populate claims tables with verified claims and the other process calling the same cursor with different parameters to display potential claims.

please supply some support for your assertion that implicit cursors are always preferable. less overhead? less typing? less filling? tastes great?

"Daniel Morgan" <damorgan_at_x.washington.edu> wrote in message news:1067709618.149457_at_yasure...
> mcstock wrote:
>
> >really need more info about your process, plus this trigger looks
incomplete
> >
> >basically your no data found is going to refer to SUPTR, where you're
> >looking for two records that match values from the record your about to
> >insert into MASUPTR
> >
> >are you sure both records exist? if one does and one doesn't, you'll get
the
> >NO_DATA_FOUND
> >
> >if that's the case you'll probably need to do two selects, then check the
> >values with PL/SQL expressions
> >
> >couple suggestions:
> >[_] you don't need the REFERENCING clause unless you're changing the NEW
and
> >OLD references
> >[_] you don't need the GROUP BY clause unless your using an aggregate
> >function like MAX, SUM, etc (GREATEST is a scalar function)
> >[_] always include an exception handler in your code to catch expected
> >exceptions, or document that there are none
> >[_] consider using explicit cursors for single-row selects -- i believe
it's
> >still true that they have just a bit less overhead than an implicit
cursor
> >
> >
> >
> Good advice with one exception. Implicit cursors are always referable to
> explicit cursors.
>
> And now, having used the word 'always' I await correction hoping to
> learn from it. Perhaps I should
> have said 'almost always' but then I wouldn't get the added benefit of
> learning from Jonathan, Richard,
> Howard, Sybrand, or whoever takes the bait.
>
> --
> 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 Sat Nov 01 2003 - 14:10:08 CST