Re: Which rows cause exception

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 -

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
damorgan_at_x.washington.edu (replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org