Re: Which rows cause exception

Shakespeare wrote:
> DA Morgan schreef:

>> 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.

If using FORALL you want to use the SAVE EXCEPTIONS syntax for trapping errors in insert, update, and/or delete.

-- 
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.org