Re: Which rows cause exception

ddf wrote:
> 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 -

Well then perhaps the first thing that should happen is patching to 9.2.0.8. <g> But you are correct I missed that too.

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