Re: Which rows cause exception
From: Shakespeare <whatsin_at_xs4all.nl>
Date: Thu, 20 Nov 2008 22:02:27 +0100
Message-ID: <4925d06c$0$196$e4fe514c@news.xs4all.nl>
>>> 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 -
Date: Thu, 20 Nov 2008 22:02:27 +0100
Message-ID: <4925d06c$0$196$e4fe514c@news.xs4all.nl>
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.
Then could you explain this remark:
* ... LOG ERRORS - effectively it turns array processing into single row * processing, so it adds an expense at the moment of inserting, even * though it saves you the overhead of an array rollback if a duplicate * gets into the data. * ~ Jonathan Lewis / comp.databases.oracle.server / 13-Aug-2006
Why would this be better then a cursor loop?
I agree that a cursor loop is not the best for (large) bulk inserts etc.
but to me it looks like LOG_ERRORS takes away the advantages of using FORALL. But I had a long day and may be missing something here.....
Shakespeare Received on Thu Nov 20 2008 - 15:02:27 CST