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@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx] On Behalf Of Steve Baldwin
Sent: Tuesday, December 06, 2005 3:32 PM
To: oracle-l@xxxxxxxxxxxxx
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

Other related posts: