Re: Which rows cause exception
Date: Thu, 20 Nov 2008 13:06:41 -0800 (PST)
On Nov 20, 2:35 pm, DA Morgan <damor..._at_psoug.org> wrote:
> ddf wrote:
> > On Nov 20, 12:47 pm, Michael Austin <maus..._at_firstdbasource.com>
> > wrote:
> >> 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
> damor..._at_x.washington.edu (replace x with u to respond)
> Puget Sound Oracle Users Groupwww.psoug.org- Hide quoted text -
> - Show quoted text -
Which isn't available in 22.214.171.124, the release he's reported as using:
"Oracle 9.2.05 SE "
I guess we both missed that in the original post.
David Fitzjarrell Received on Thu Nov 20 2008 - 15:06:41 CST