update multiple rows continue past exceptions
lora
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;
Thomas Kellerer
Use the error logging clause:
Details are here:
http://download-uk.oracle.com/docs/cd/B19306_01/appdev.102/b14258/d_errlog.htm#ARPLS680
http://download-uk.oracle.com/docs/cd/B19306_01/server.102/b14231/tables.htm#ADMIN10261
http://download-uk.oracle.com/docs/cd/B19306_01/server.102/b14200/statements_9014.htm#BCEGDJDJ
The examples all use INSERT, but you can use that with UPDATE just as well
http://download-uk.oracle.com/docs/cd/B19306_01/server.102/b14200/statements_10007.htm#BCEEAAGC
Thomas
jimmyb
http://www.oracle.com/technology/oramag/oracle/09-mar/o29plsql.html
Scroll half-way down to "DML Error Logging"
Mladen Gogala
Kenneth Koenraadt
> 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:
>
>
> 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
Thomas Kellerer
> 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 ?
That's in fact documented:
http://download-uk.oracle.com/docs/cd/B19306_01/server.102/b14200/statements_9014.htm#BGBEIACB
"The following conditions cause the statement to fail and roll back without invoking the error logging capability:"
[...]
- Any update operation UPDATE or MERGE that raises a unique constraint or index violation).
I didn't see that either the first time...
Thomas
Steve Howard
>
> 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.
jimmyb
They used to do an UPDATE when an exception was raised? That should be
a bad coding practice in every shop, JMO.
Mladen Gogala
> They used to do an UPDATE when an exception was raised? That should be a
> bad coding practice in every shop, JMO.
Why is that?
Shakespeare
Not if the exception is a dup-val-on-index. Its a practice seen all around!
Shakespeare
jimmyb
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.
Shakespeare
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
lora
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...@mail-online.dk> wrote:
> On 11 Dec., 23:47, Thomas Kellerer <OTPXDAJCS...@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......
> - Kenneth Koenraadt- Hide quoted text -
>
> - Show quoted text -
jimmyb
>
> - 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) ;
jimmyb
>
> - Show quoted text -
Are you using large objects or other object types? I don't think those
are supported.
yossarian
>>> 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!
IMHO the more efficient way is to try to update the row first, then
perform an insert if sql%rowcount=0.
Y.