Re: Which rows cause exception
Date: Thu, 20 Nov 2008 12:35:55 -0800
> On Nov 20, 12:47 pm, Michael Austin <maus..._at_firstdbasource.com>
>> Terry Dykstra wrote: >>> Oracle 9.2.05 SE >>> In a stored procedure I have code like this: >>> INSERT INTO DPRS_WELL (PRODUCTION_MONTH,UWI,....) >>> SELECT ad_prod_date,UWI,... >>> FROM DPRS_WELL_LOAD >>> WHERE PRODUCTION_MONTH = add_months(ad_prod_date,-1); >>> EXCEPTION >>> WHEN DUP_VAL_ON_INDEX THEN >>> RAISE_APPLICATION_ERROR(-20001,'Duplicate UWI', true); >>> WHEN VALUE_ERROR THEN >>> RAISE_APPLICATION_ERROR(-20002,'Value error', true); >>> Is there any way I can determine in the exception block which row(s) caused >>> the exception? >> Add an exception table and in the exception routine - insert the data in >> the exception table...- Hide quoted text - >> >> - Show quoted text -
> To do that he'd need to use a cursor and a loop to insert the data; as
> it stands now he won't be able to isolate the 'offending' record (note
> the insert into ... select ... statement he's using).
> David Fitzjarrell
A cursor loop is the wrong answer to essentially any question. The best solution is to use FORALL and look at the exception array though I did suggest looking at the DBMS_ERRLOG package.
-- Daniel A. Morgan Oracle Ace Director & Instructor University of Washington damorgan_at_x.washington.edu (replace x with u to respond) Puget Sound Oracle Users Group www.psoug.orgReceived on Thu Nov 20 2008 - 14:35:55 CST