Re: Which rows cause exception

From: ddf <oratune_at_msn.com>
Date: Thu, 20 Nov 2008 13:06:41 -0800 (PST)
Message-ID: <046a3f2b-44d4-4fc8-9ad2-1ad95dc8641b@j38g2000yqa.googlegroups.com>


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 9.2.0.5, 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

Original text of this message