Prev: Optimizer 10053 trace file, strange choises made by Oracle??
Next: Compression
From: jimmyb on 14 Dec 2009 15:10
On Dec 14, 10:39 am, Shakespeare <what...(a)xs4all.nl> wrote:
> jimmyb schreef:
>
> > On Dec 13, 4:21 pm, Steve Howard <stevedhow...(a)gmail.com> wrote:
> >> On Dec 12, 1:41 am, Mladen Gogala <gogala.mla...(a)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.

From: Shakespeare on 14 Dec 2009 15:34
jimmyb schreef:
> On Dec 14, 10:39 am, Shakespeare <what...(a)xs4all.nl> wrote:
>> jimmyb schreef:
>>
>>> On Dec 13, 4:21 pm, Steve Howard <stevedhow...(a)gmail.com> wrote:
>>>> On Dec 12, 1:41 am, Mladen Gogala <gogala.mla...(a)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
From: lora on 14 Dec 2009 16:01
Yikes, the DBMS_ERRLOG is not working on my end.

SQL> EXEC dbms_errlog.create_error_log( 'MYTAB');
BEGIN dbms_errlog.create_error_log( 'MYTAB'); END;

*
ERROR at line 1:
ORA-20069: Unsupported column type(s) found: REQUEST2 RESPONSE
ORA-06512: at "SYS.DBMS_ERRLOG", line 233
ORA-06512: at line 1



On Dec 13, 3:14 pm, Kenneth Koenraadt <k...(a)mail-online.dk> wrote:
> On 11 Dec., 23:47, Thomas Kellerer <OTPXDAJCS...(a)spammotel.com> wrote:
>
>
>
>
>
> > lora wrote on 11.12.2009 23:41:
>
> > > Hello all,
>
> > > I need to update as many rows as possible. The issue is that one row
> > > that fails causes the entire transaction to fail.
>
> > > How do I get around this? I'd like to complete all UPDATE rows that
> > > don't have the exception. The below doesn't seem to work.
>
> > > I'm using Oracle 10g
>
> > > Thanks a bunch!
>
> > > BEGIN
> > > UPDATE MYTAB SET request = REPLACE(request, '$tpsToModify',
> > > 'tpsToModify') WHERE request like '%$tpsToModify%';
> > >   EXCEPTION
> > >      when dup_val_on_index then
> > >          dbms_output.put_line('DUPLICATE RECORD');
>
> > > END;
>
> > Use the error logging clause:
>
> > Details are here:http://download-uk.oracle.com/docs/cd/B19306_01/appdev..102/b14258/d_e......
>
> > The examples all use INSERT, but you can use that with UPDATE just as wellhttp://download-uk.oracle.com/docs/cd/B19306_01/server.102/b14200/sta...
>
> > Thomas- Skjul tekst i anførselstegn -
>
> > - Vis tekst i anførselstegn -
>
> Hi Thomas,
>
> That's interesting : The online doc clearly states that UPDATE works
> just as INSERT's regarding error logging..however, I have this simple
> test case, which works with INSERT's, but not UPDATE's. The OP wanted
> just that, so it may not work for him. Any thoughts ?
>
> -------------------------
>
> SQL> create table daf as select * from dba_objects where rownum <=5;
>
> Tabel er oprettet.
>
> SQL> create unique index daf_object_id_idx on daf(object_id);
>
> Indeks er oprettet.
>
> SQL> exec DBMS_ERRLOG.CREATE_ERROR_LOG('DAF','DAF_ERR');
>
> PL/SQL-procedure er udf°rt.
>
> -- Now try to violate unique constraint with an INSERT
>
> SQL> insert into daf select * from daf where rownum <=1 log errors
> into daf_err ('INSERTING') reject limit unlimited;
>
> 0 rµkker er oprettet.
>
> -- Correct, row isn't inserted, and error is recorded in the error log
> table
> SQL> select count(*) from daf_err;
>
>   COUNT(*)
> ----------
>          1
>
> -- Now try to violate unique constraint with an UPDATE
>
> SQL> select object_id from daf order by object_id;
>
>  OBJECT_ID
> ----------
>         15
>         20
>         28
>         29
>         44
>
> SQL> update daf set object_id = 20 where object_id = 15 log errors
> into daf_err ('UPDATING') reject limit unlimited;
> update daf set object_id = 20 where object_id = 15 log errors into
> daf_err ('UPDATING') reject limit unlimited
> *
> FEJL i linie 1:
> ORA-00001: unik begrµnsning (A.DAF_OBJECT_ID_IDX) er overtrÕdt
>
> -- Wrong, Got execption, and row isn't recorded in DAF_ERR
> SQL> select count(*) from daf_err;
>
>   COUNT(*)
> ----------
>          1
>
> - Kenneth Koenraadt- Hide quoted text -
>
> - Show quoted text -

From: jimmyb on 14 Dec 2009 16:02
On Dec 14, 12:34 pm, Shakespeare <what...(a)xs4all.nl> wrote:
> jimmyb schreef:
>
>
>
> > On Dec 14, 10:39 am, Shakespeare <what...(a)xs4all.nl> wrote:
> >> jimmyb schreef:
>
> >>> On Dec 13, 4:21 pm, Steve Howard <stevedhow...(a)gmail.com> wrote:
> >>>> On Dec 12, 1:41 am, Mladen Gogala <gogala.mla...(a)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) ;

From: jimmyb on 14 Dec 2009 16:28
On Dec 14, 1:01 pm, lora <anjela_...(a)yahoo.com> wrote:
> Yikes, the DBMS_ERRLOG is not working on my end.
>
> SQL> EXEC dbms_errlog.create_error_log( 'MYTAB');
> BEGIN dbms_errlog.create_error_log( 'MYTAB'); END;
>
> *
> ERROR at line 1:
> ORA-20069: Unsupported column type(s) found: REQUEST2 RESPONSE
> ORA-06512: at "SYS.DBMS_ERRLOG", line 233
> ORA-06512: at line 1
>
> On Dec 13, 3:14 pm, Kenneth Koenraadt <k...(a)mail-online.dk> wrote:
>
>
>
> > On 11 Dec., 23:47, Thomas Kellerer <OTPXDAJCS...(a)spammotel.com> wrote:
>
> > > lora wrote on 11.12.2009 23:41:
>
> > > > Hello all,
>
> > > > I need to update as many rows as possible. The issue is that one row
> > > > that fails causes the entire transaction to fail.
>
> > > > How do I get around this? I'd like to complete all UPDATE rows that
> > > > don't have the exception. The below doesn't seem to work.
>
> > > > I'm using Oracle 10g
>
> > > > Thanks a bunch!
>
> > > > BEGIN
> > > > UPDATE MYTAB SET request = REPLACE(request, '$tpsToModify',
> > > > 'tpsToModify') WHERE request like '%$tpsToModify%';
> > > >   EXCEPTION
> > > >      when dup_val_on_index then
> > > >          dbms_output.put_line('DUPLICATE RECORD');
>
> > > > END;
>
> > > Use the error logging clause:
>
> > > Details are here:http://download-uk.oracle.com/docs/cd/B19306_01/appdev.102/b14258/d_e......
>
> > > The examples all use INSERT, but you can use that with UPDATE just as wellhttp://download-uk.oracle.com/docs/cd/B19306_01/server.102/b14200/sta....
>
> > > Thomas- Skjul tekst i anførselstegn -
>
> > > - Vis tekst i anførselstegn -
>
> > Hi Thomas,
>
> > That's interesting : The online doc clearly states that UPDATE works
> > just as INSERT's regarding error logging..however, I have this simple
> > test case, which works with INSERT's, but not UPDATE's. The OP wanted
> > just that, so it may not work for him. Any thoughts ?
>
> > -------------------------
>
> > SQL> create table daf as select * from dba_objects where rownum <=5;
>
> > Tabel er oprettet.
>
> > SQL> create unique index daf_object_id_idx on daf(object_id);
>
> > Indeks er oprettet.
>
> > SQL> exec DBMS_ERRLOG.CREATE_ERROR_LOG('DAF','DAF_ERR');
>
> > PL/SQL-procedure er udf°rt.
>
> > -- Now try to violate unique constraint with an INSERT
>
> > SQL> insert into daf select * from daf where rownum <=1 log errors
> > into daf_err ('INSERTING') reject limit unlimited;
>
> > 0 rµkker er oprettet.
>
> > -- Correct, row isn't inserted, and error is recorded in the error log
> > table
> > SQL> select count(*) from daf_err;
>
> >   COUNT(*)
> > ----------
> >          1
>
> > -- Now try to violate unique constraint with an UPDATE
>
> > SQL> select object_id from daf order by object_id;
>
> >  OBJECT_ID
> > ----------
> >         15
> >         20
> >         28
> >         29
> >         44
>
> > SQL> update daf set object_id = 20 where object_id = 15 log errors
> > into daf_err ('UPDATING') reject limit unlimited;
> > update daf set object_id = 20 where object_id = 15 log errors into
> > daf_err ('UPDATING') reject limit unlimited
> > *
> > FEJL i linie 1:
> > ORA-00001: unik begrµnsning (A.DAF_OBJECT_ID_IDX) er overtrÕdt
>
> > -- Wrong, Got execption, and row isn't recorded in DAF_ERR
> > SQL> select count(*) from daf_err;
>
> >   COUNT(*)
> > ----------
> >          1
>
> > - Kenneth Koenraadt- Hide quoted text -
>
> > - Show quoted text -- Hide quoted text -
>
> - Show quoted text -

Are you using large objects or other object types? I don't think those
are supported.
First  |  Prev  |  Next  |  Last
Pages: 1 2 3 4
Prev: Optimizer 10053 trace file, strange choises made by Oracle??
Next: Compression