Hi,
We are facing some issue when we use the DML error logging. Below are the details
- We have a table where we have a unique constraint and NOT NULL constraint on few columns.
- In our programs we take data from some tables, collect it in a temp table to insert it into the table mentioned above.
- ERROR log table is created using dbms_errlog.create_error_log
We have some records in the temp table violating the NOT NULL constraint.
Now, when we insert data using a cursor-For All loop, this works fine and we can see that " no. of records in temp table = no. of records inserted + no. of records in the error log"
When we try to do use direct insert, we can see that the no. of records in error log table are more than the actual no. of records violating the constraint. Some of the records causing issue are inserted twice. (Append hint is not used. Simply 'Insert into table_name select * from temp_table' )
Similar issue is observed when we use MERGE & the UNIQUE constraint is violated.
What is the reason behind this? Is there any way to resolve this?
Thanks,
Devendra