Re: Which rows cause exception
From: Mladen Gogala <gogala.mladen_at_gmail.com>
Date: Mon, 24 Nov 2008 11:47:37 +0000 (UTC)
Message-ID: <gge48p$cvt$3@solani.org>
>> 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?
>
> http://www.psoug.org/reference/dbms_errlog.html
Date: Mon, 24 Nov 2008 11:47:37 +0000 (UTC)
Message-ID: <gge48p$cvt$3@solani.org>
On Thu, 20 Nov 2008 12:33:47 -0800, DA Morgan 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?
>
> http://www.psoug.org/reference/dbms_errlog.html
That will not work in Oracle 9i. You need version 10 to use DBMS_ERRLOG.
-- Mladen Gogala http://mgogala.freehostia.comReceived on Mon Nov 24 2008 - 05:47:37 CST