Re: update multiple rows continue past exceptions
From: jimmyb <jimmybrock_at_gmail.com>
Date: Mon, 14 Dec 2009 13:02:25 -0800 (PST)
Message-ID: <643932f1-f33c-4c92-a812-f07c18126657_at_z41g2000yqz.googlegroups.com>
On Dec 14, 12:34 pm, Shakespeare <what..._at_xs4all.nl> wrote:
> 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- Hide quoted text -
>
> - Show quoted text -
Date: Mon, 14 Dec 2009 13:02:25 -0800 (PST)
Message-ID: <643932f1-f33c-4c92-a812-f07c18126657_at_z41g2000yqz.googlegroups.com>
On Dec 14, 12:34 pm, Shakespeare <what..._at_xs4all.nl> wrote:
> 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- Hide quoted text -
>
> - Show quoted text -
I let the cursors determine if a record should be updated or inserted.
- person_id primary key in both tables
- only update records that exists in both source and target tables
- and the data has changed select u..person_id, s.name, s.a_email_addr from source_table s , users u where s.person_id = u.person_id and ( nvl(s.name,' ') != nvl(u.name,' ') or nvl(s.a_email_addr, ' ') != nvl(u.a_email_addr,' ') ) ;
- insert records that exists in source table, but not in the target table select s..person_id, s.name, s.a_email_addr from source_table s where not exists ( select null from users u where u.person_id = s.person_id) ;