Re: update multiple rows continue past exceptions

From: jimmyb <jimmybrock_at_gmail.com>
Date: Mon, 14 Dec 2009 12:10:24 -0800 (PST)
Message-ID: <e455e91e-2a81-43cd-8fc3-487d44e42394_at_m7g2000prd.googlegroups.com>

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. Received on Mon Dec 14 2009 - 14:10:24 CST