Get the information about source row causing "ORA-00001 unique constraint violated" error in Oracle

2
votes

Is there any way to get the complete row or selected columns of source row/table which is causing 

 "ORA-00001 unique constraint violated" error.

Here is a small example.

create table DW_DATA (
                      file_id number,
                      process_date date,
                      record_info varchar2(50),
                      constraint uk_pd_ri unique (process_date, 
                                                  record_info)
);

After create, Inserting first record,

insert into DW_DATA
             values (100, 
                     '10-Jul-2018', 
                     'Information about row');

commit;

Now, I am inserting a new row.

insert into DW_DATA
             values (200, 
                    '10-Jul-2018', 
                    'Information about row');

It will throw an error "ORA-00001 unique constraint violated".

So, my question is, is it possible to get the file_id of source row i.e. 100 using DBMS_ERRLOG concept. Or is there any other approach available to get the info about source row.

3
sqloracleplsql
You can use insert into ... log errors into ... but that would not cause the insert to fail! - a_horse_with_no_name
log errors will give me the information about new row i.e. row having file_id 200 if I create the ERR$ table using DBMS_ERRLOG package. - Sanket
Performance would take a hit, but you could use a merge, join the input to the source table, then use a when matched / not matched to divide the 2 scenarios up, while having access to both records. - Andrew

3 Answers

0
votes

When I run the query, I get:

ORA-00001: unique constraint (FIDDLE_RSGKXYATHHTELGHIJXGD.UK_PD_RI) violated

This contains the unique constraint name -- "UK_PD_RI".

You can then look up the data based on the columns used for the constraint:

select *
from dw_data
where process_date = '10-Jul-2018' and record_info = 'Information about row';

Admittedly, this is not a single step process. You can do something more directly using error logging. That is a little more involved. Here is a good place to read about it. However, I don't think error logging will provide the rowid of the existing row for a unique constraint violation.

0
votes
select process_date, record_info, count(*) from DW_DATA 
group by process_date, record_info
having count(*) >1

you will have the list of UK values which are duplicating.

0
votes

You just need to use MERGE statement:

MERGE INTO DW_DATA t1 
USING (
select 200 as file_id,
'10-Jul-2018' as process_date,
'Information about row' as record_info from dual 
) t2 
ON (t1.process_date= t2.process_date and t1.record_info = t2.record_info) 
WHEN MATCHED THEN UPDATE SET file_id = t2.file_id 
WHEN NOT MATCHED THEN INSERT (file_id, process_date,recorD_info ) VALUES (t2.file_id, t2.process_date, t2.recorD_info);

Because MERGE is designed for this purpose - update when exists and insert when not. Please check out the docs for this command.