• DML错误日志使用语法
  • 一、实例演示
  • 1、创建错误日志表
  • 2、声明 log error子句
  • 2.1 不设置reject limit插入数据
  • 2.2 加入reject limit 再进行插入
  • 二 、使用DML错误记录注意事项
  • 三、当基表中含有不支持的数据类型列时，创建Errors表的正确语法



DML错误日志这个功能提供一个机制来使得你的一百万行数据插入不会仅仅由于几行数据有问题而失败。


这个特性在10gR2中引入，类似于Sql*loader的错误日志功能。它的基本原理就是将任何可能导致失败的记录转移，放入到一个张错误记录表中。log errors 语句在其他DML语句(update.delete,merge)中同样适用。

DML错误日志使用语法

1、使用DBMS_ERRLOG.CREATE_ERROR_LOG(op_table,error_log_table)来创建错误日志表
2、在insert 语句中声明log error子句。

一、实例演示

1、创建错误日志表

execute DBMS_ERRLOG.CREATE_ERROR_LOG('emp','emp_error');

或者在plsql中可以这样调用

begin
 DBMS_ERRLOG.CREATE_ERROR_LOG('emp','emp_error');
 end；

查看刚刚创建错误日志记录表的结构

desc  emp_error

oracle sql 高级编程学习笔记（二十九）_DML错误日志注意事项

可以看到，错误日志表 emp_error中所有列都创建为varchar2(4000).使得绝大多数的数据类型都可以插入到日志表中。还有ora_err_tag$列允许用户放入自定义的数据，以便进行调试（即ETL过程所处的步骤，或其他性质的内容）

2、声明 log error子句

加入关键字log errors into 并声明你的错误表名就可以了。或者你可以告诉oracle 在放弃插入并取消语句之前允许多少个错误出现。这是通过reject limit子句来实现。主要reject limit默认值时0，因此如果有一个错误，语句就会取消并回滚。不过这个单独的错误也会放入到日志表中。大多情况下，你需要将reject limit 设置为unlimited从而允许插入语句完成而不管有多少个错误。
–插入错误日志演示
– 为emp表创建主键

alter table  emp add constraint EMP_PK primary key (EMPLOYEE_ID)

–tmp_emp 表中有如下数据

oracle sql 高级编程学习笔记（二十九）_DML错误日志注意事项_02

2.1 不设置reject limit插入数据

insert into  emp ( employee_id, first_name, last_name)
 select  t.* from  tmp_emp t 
 log errors into emp_error ;
 --查看错误信息
 select  t.ora_err_mesg$,t.employee_id,t.first_name from  emp_error t;

oracle sql 高级编程学习笔记（二十九）_oracle DML错误日志演示_03


–再看其他数据有没有有插入

select  t.* from  emp t  where t.employee_id>=300;

很明显由于没有设置reject limit值，所以默认是只要由一个错误就开始回滚。

2.2 加入reject limit 再进行插入

insert into  emp ( employee_id, first_name, last_name)
 select  t.* from  tmp_emp t 
 log errors into emp_error 
 reject limit unlimited ;

oracle sql 高级编程学习笔记（二十九）_log error实例演示_04


注意 最后的commit 不能少。已经有3行数据插入

再来看日志表 又多了条相同数据

oracle sql 高级编程学习笔记（二十九）_oracle sql高级编程学习笔记_05

–再看其他数据有没有插入

select t.* from emp t where t.employee_id>=300;

oracle sql 高级编程学习笔记（二十九）_DML错误日志注意事项_06

所以当reject limit 为unlimited 的error loggin 子句，可以使得即使插入数据有失败的记录，但是插入语句也可以完成。大家需要注意的时，如果在插入数据后，混滚了事务，当然数据不会插入到操作表中，但是错误的记录依然会保存下来。

二 、使用DML错误记录注意事项

1、log errors 子句不引起隐式提交。错误记录的插入是有一个自治事务来处理的，也就是说
即使返回了错误并将错误记录插入到了错入日志表，你也可以提交或回滚插入到基表中的所有记录。即使事务进行了回滚。错误日志表中的记录也将会保留。
2、log errors子句不会禁用append 提示。如果使用了append 提示，对于基表的插入将会使用直接路径写入机制来完成。但是，任何到errors表的写入都不会使用直接路径写入。
3、违反唯一键或索引约束的直接路径插入运算将会引起语句失败并进行回滚。
4、任何违反唯一键或索引约束的更新运算将会引起语句失败并进行回滚。
5、任何违反延迟约束的运算都将会引起语句失败并进行回滚。
6、log errors 子句不会追踪log，long或对象类型列的值。它可以与包含这些不支持的数据类型列的表一起使用，但是这些不支持的数据类型的列将不会插入到errors表中。要想为一张包含不支持数据类型列的表创建errors表，必须使用create_error_log 存储过程的skip_unsupported参数。这个参数的默认值为false。
7、经过实验，不支持with语句。有待验证，可能是自己还没掌握with语句种使用dml错误日志的语法。

三、当基表中含有不支持的数据类型列时，创建Errors表的正确语法

begin 
        DBMS_ERRLOG.CREATE_ERROR_LOG(
     err_log_table_owner  => 'owener', 
     dml_table_name  => 'table_name',
     err_log_table_name  => 'err_log_table_name',
     err_log_table_space  => NULL,
     skip_unsupported  => true);
     end ;