Re: Which rows cause exception

"ddf" <oratune_at_msn.com> wrote in message news:ac950571-c282-4000-8827-cde5ef762327_at_u14g2000yqg.googlegroups.com... 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

That's what I was afraid off. I guess I'll have to a use BULK COLLECT with a FORALL to handle the exception.

-- 
Terry Dykstra