Re: update multiple rows continue past exceptions

From: jimmyb <>
Date: Mon, 14 Dec 2009 12:10:24 -0800 (PST)
Message-ID: <>

On Dec 14, 10:39 am, Shakespeare <> wrote:
> jimmyb schreef:
> > On Dec 13, 4:21 pm, Steve Howard <> wrote:
> >> On Dec 12, 1:41 am, Mladen Gogala <> wrote:
> >>> Why not simply use the merge statement?
> >>> --
> >> 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

Original text of this message