Re: Which rows cause exception

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 Received on Thu Nov 20 2008 - 13:17:31 CST