Given this example (DUP_VAL_ON_INDEX Exception), is it possible to capture the values that violated the constraint so they may be logged?
Would the approach be the same if there are multiple violations generated by a bulk insert?
BEGIN
-- want to capture '01' and '02'
INSERT INTO Employee(ID)
SELECT ID
FROM (
SELECT '01' ID FROM DUAL
UNION
SELECT '02' ID FROM DUAL
);
EXCEPTION
WHEN DUP_VAL_ON_INDEX THEN
-- log values here
DBMS_OUTPUT.PUT_LINE('Duplicate value on an index');
END;
Ideally, I would suggest using DML error logging. For example
Create the error log table
begin
dbms_errlog.create_error_log( dml_table_name => 'EMPLOYEE',
err_log_table_name => 'EMPLOYEE_ERR' );
end;
Use DML error logging
BEGIN
insert into employee( id )
select id
from (select '01' id from dual
union all
select '02' from dual)
log errors into employee_err
reject limit unlimited;
END;
For every row that fails, this will log the data for the row into the EMPLOYEE_ERR table along with the exception. You can then query the error log table to see all the errors rather than getting just the first row that failed.
If creating the error log table isn't an option, you could move from SQL to PL/SQL with bulk operations. That will be slower but you could use the SAVE EXCEPTIONS clause of the FORALL statement to create a nested table of exceptions that you could then iterate over.
