Re: Which rows cause exception

From: Shakespeare <>
Date: Thu, 20 Nov 2008 22:02:27 +0100
Message-ID: <4925d06c$0$196$>

DA Morgan schreef:

> ddf wrote:
>> On Nov 20, 12:47 pm, Michael Austin <>
>> wrote:

>>> Terry Dykstra wrote:
>>>> Oracle 9.2.05 SE
>>>> In a stored procedure I have code like this:
>>>> SELECT ad_prod_date,UWI,...
>>>> WHERE PRODUCTION_MONTH = add_months(ad_prod_date,-1);
>>>> RAISE_APPLICATION_ERROR(-20001,'Duplicate UWI', true);
>>>> 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 / / 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

Original text of this message