Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> Question on SERVERERROR trigger

Question on SERVERERROR trigger

From: Steve Baldwin <stbaldwin_at_multiservice.com>
Date: Wed, 7 Dec 2005 07:32:14 +1100
Message-Id: <20051206203222.22F3124BC23@turing.freelists.org>


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
Received on Tue Dec 06 2005 - 14:34:26 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US