Oracle Database Discussions

Hi!

I'm running an update statement which updates key fields in a table. However, I noticed that the statement fails when facing unique constraint violation. [ORA-00001: unique constraint (schema.tablename) violated]

Sample data:

Statement:

update addresses_tbl set emplid = '6789' where emplid = '1234';

Ideal result:

I have already researched on this and found out about create_error_log, however, i don't think it works.

--1
SQL> exec dbms_errlog.create_error_log('addresses_tbl', 'addresses_tbl2');
PL/SQL procedure successfully completed
SQL> update addresses_tbl set emplid = '6789' where emplid = '1234'
2 log errors into addresses_tbl2 ('x') reject limit unlimited;
update addresses_tbl set emplid = '6789' where emplid = '1234'
log errors into addresses_tbl2 ('x') reject limit unlimited
ORA-00001: unique constraint (schema.addresses_tbl) violated
SQL>

--2

SQL> exec dbms_errlog.create_error_log('addresses_tbl');

PL/SQL procedure successfully completed

SQL>

SQL> update addresses_tbl set emplid = '6789' where emplid = '1234'

2 log errors into err$_addresses_tbl ('x') reject limit unlimited;

update addresses_tbl set emplid = '6789' where emplid = '1234'

log errors into err$_addresses_tbl ('x') reject limit unlimited

ORA-00001: unique constraint (schema.addresses_tbl) violated

SQL>

Then there's hints, which don't work as well.

--3

SQL> update /*+ ignore_row_on_dupkey_index(addresses_tbl,addresses_tbl) */ addresses_tbl set emplid = '6789' where emplid = '1234';

update /*+ ignore_row_on_dupkey_index(addresses_tbl,addresses_tbl) */ addresses_tbl set emplid = '6789' where emplid = '1234'

ORA-38917: IGNORE_ROW_ON_DUPKEY_INDEX hint disallowed for this operation

SQL>

Any ideas?

Thanks!