Re: update multiple rows continue past exceptions
Date: Mon, 14 Dec 2009 12:10:24 -0800 (PST)
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, 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