Pfthb

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.

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.

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.

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.