Re: Question on SERVERERROR trigger

Agreed but sometimes you know this wont happen, due to primary keys, but if the primary keys are a missing it could happen, and you want to cover only those very strange situations.

I am curious if someone will have a better idea, I am interested in this too.

Christo Kutrovsky
Senior Database/System Administrator
The Pythian Group

On 12/7/05, Powell, Mark D <mark.powell_at_eds.com> wrote:
> Steve, I think that the no_data_found and too_many_rows error should
> really be handled at the point where they occur. In some cases not
> finding an expected row may be OK and the code can continue. In the
> case of too many rows some times the code can process the first row
> found while creating a message that the data needs to be looked at. In
> either case application specific data values helpful in resolving the
> issue are going to be available at the point where the error occurs.
>
> IMHO -- Mark D Powell --
>
>
> -----Original Message-----
> From: oracle-l-bounce_at_freelists.org
> [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Steve Baldwin
> Sent: Tuesday, December 06, 2005 3:32 PM
> To: oracle-l_at_freelists.org
> Subject: Question on SERVERERROR trigger
>
> Hi list,
>
> I'm looking to implement some global error handling code with a database
> (9.2.0.7) AFTER SERVERERROR trigger. My question relates to a PL/SQL
> situation in which the trigger does not fire. If I have code such as
> this ...
>
> SELECT xxx
> INTO l_var
> FROM some_table
> WHERE ...
>
> If this returns no rows, in PL/SQL I get an exception raised
> (NO_DATA_FOUND). However, as it states in the documentation, this does
> *not* cause the AFTER SERVERERROR trigger to fire. The documentation
> says this is because this error (1403) along with 1422 and 1423 are not
> 'true errors'. Well, for something that is not a true error, they
> certainly stop the PL/SQL engine in its tracks.
>
> About the only solution I can think of is to do something like this ...
>
> :
> BEGIN
> SELECT xxx
> INTO l_var
> FROM some_table
> WHERE ...
> EXCEPTION
> WHEN NO_DATA_FOUND THEN RAISE a_REAL_exception;
> WHEN TOO_MANY_ROWS THEN RAISE another_REAL_exception; END;
> :
>
> A bit of a nuisance :-(
>
> Does anyone else have a better idea?
>
> Thanks and regards,
>
> Steve
>
>
> --
> http://www.freelists.org/webpage/oracle-l
>
>
> --
> http://www.freelists.org/webpage/oracle-l
>
>
>

--
Christo Kutrovsky
Database/System Administrator
The Pythian Group
--
http://www.freelists.org/webpage/oracle-l