捕获触发DUP_VAL_ON_INDEX的值 [英] Capture values that trigger DUP_VAL_ON_INDEX

问题描述

给出此示例( DUP_VAL_ON_INDEX异常)，是否有可能捕获违反约束的值以便将其记录下来?

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;

推荐答案

理想情况下，我建议使用DML错误日志记录.例如

Ideally, I would suggest using DML error logging. For example

创建错误日志表

begin
  dbms_errlog.create_error_log( dml_table_name => 'EMPLOYEE',
                                err_log_table_name => 'EMPLOYEE_ERR' );
end;

使用DML错误日志记录

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;

对于失败的每一行，这会将行的数据以及异常记录到EMPLOYEE_ERR表中.然后，您可以查询错误日志表以查看所有错误，而不仅仅是获得失败的第一行.

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.

如果不能选择创建错误日志表，则可以通过批量操作将SQL从SQL转移到PL/SQL.这样会比较慢，但是您可以使用FORALL语句的SAVE EXCEPTIONS子句创建嵌套的异常表，然后可以对其进行迭代.

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.

这篇关于捕获触发DUP_VAL_ON_INDEX的值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！