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@xxxxxxx> 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@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 > > > -- > //www.freelists.org/webpage/oracle-l > > > -- > //www.freelists.org/webpage/oracle-l > > > -- Christo Kutrovsky Database/System Administrator The Pythian Group -- //www.freelists.org/webpage/oracle-l