Re: Which rows cause exception
From: Terry Dykstra <tddykstra_at_forestoil.ca>
Date: Mon, 24 Nov 2008 16:03:02 GMT
Message-ID: <WeAWk.2686$jr4.1023@edtnps82>
>>>>>> 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?
Date: Mon, 24 Nov 2008 16:03:02 GMT
Message-ID: <WeAWk.2686$jr4.1023@edtnps82>
"DA Morgan" <damorgan_at_psoug.org> wrote in message
news:1227305408.374272_at_bubbleator.drizzle.com...
> 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 - >> >> Which isn't available in 9.2.0.5, the release he's reported as using: >> >> "Oracle 9.2.05 SE " >> >> I guess we both missed that in the original post. >> >> >> David Fitzjarrell > > 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
If I could, I would have done that a long time ago. The 3rd party Oracle forms application has a dependency that causes all kinds of locking issues when using anything above 9205. 9206 made changes in that area and I got burned big time trying to go that release when it came available. Next spring I can upgrade to a newer version of the application that will use 10.2. But for now I'm stuck with 9205.
-- Terry DykstraReceived on Mon Nov 24 2008 - 10:03:02 CST