RE: Question on SERVERERROR trigger

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