Re: update multiple rows continue past exceptions
From: Shakespeare <whatsin_at_xs4all.nl>
Date: Mon, 14 Dec 2009 21:34:10 +0100
Message-ID: <4b26a147$0$22935$e4fe514c_at_news.xs4all.nl>
jimmyb schreef:
> On Dec 14, 10:39 am, Shakespeare <what..._at_xs4all.nl> wrote:
>
> Shakespeare, that is probablly correct. I have seen it done with the
> dup_val_on_index exception, now that you mention it.
>
> Where I work we cannot use a merge statement because of VPD. You get
> ORA-28132: Merge into syntax does not support security policies. So
> what I did was create two cursors, one for update and one for insert.
> Then use two PL/SQL blocks, one for each cursor with a FORALL
> statement. I doubt if it is faster, just another where of doing it.
>
I guess if you don't want to use exceptions, you have to check for existence of the row first, which takes an extra roundtrip to the server. And how do you keep track of records that could not be updated but should be inserted or vice versa? The exception method seems so much easier to me....
Date: Mon, 14 Dec 2009 21:34:10 +0100
Message-ID: <4b26a147$0$22935$e4fe514c_at_news.xs4all.nl>
jimmyb schreef:
> On Dec 14, 10:39 am, Shakespeare <what..._at_xs4all.nl> wrote:
>> jimmyb schreef: >> >>> On Dec 13, 4:21 pm, Steve Howard <stevedhow..._at_gmail.com> wrote: >>>> On Dec 12, 1:41 am, Mladen Gogala <gogala.mla..._at_gmail.com> wrote: >>>>> Why not simply use the merge statement? >>>>> --http://mgogala.byethost5.com >>>> This is a regular event in our shop. Our developers used to try to >>>> insert, and then handle the exception if it existed and update it. We >>>> cut our redo by a large factor when we asked them to switch to MERGE. >>> They used to do an UPDATE when an exception was raised? That should be >>> a bad coding practice in every shop, JMO. >> Not if the exception is a dup-val-on-index. Its a practice seen all around! >> >> Shakespeare
>
> Shakespeare, that is probablly correct. I have seen it done with the
> dup_val_on_index exception, now that you mention it.
>
> Where I work we cannot use a merge statement because of VPD. You get
> ORA-28132: Merge into syntax does not support security policies. So
> what I did was create two cursors, one for update and one for insert.
> Then use two PL/SQL blocks, one for each cursor with a FORALL
> statement. I doubt if it is faster, just another where of doing it.
>
I guess if you don't want to use exceptions, you have to check for existence of the row first, which takes an extra roundtrip to the server. And how do you keep track of records that could not be updated but should be inserted or vice versa? The exception method seems so much easier to me....
Shakespeare Received on Mon Dec 14 2009 - 14:34:10 CST