Oracle DML容错处理（1）

Description

dml_table_name

The name of the DML table to base the error logging table on. The name can be fully qualified (for example, emp, scott.emp, "EMP", "SCOTT"."EMP"). If a name component is enclosed in double quotes, it will not be upper cased.

err_log_table_name

The name of the error logging table you will create.

The default is the first 25 characters in the name of the DML table prefixed with‘ERR$_‘. Examples are the following:

dml_table_name: ‘EMP‘, err_log_table_name: ‘ERR$_EMP‘

dml_table_name: ‘"Emp2"‘, err_log_table_name: ‘ERR$_Emp2‘

err_log_table_owner

The name of the owner of the error logging table. You can specify the owner indml_table_name. Otherwise, the schema of the current connected user is used.

err_log_table_space

The tablespace the error logging table will be created in. If not specified, the default tablespace for the user owning the DML error logging table will be used.

skip_unsupported

When set to TRUE, column types that are not supported by error logging will be skipped over and not added to the error logging table.

When set to FALSE, an unsupported column type will cause the procedure to terminate.

The default is FALSE.

对于不支持的数据类型可以使用最后一个参数控制，如果为true，不支持类型字段将不会进入错误日志表。如果是false，在遇到不支持类型字段时执行包会报错。

各参数默认值如下：

DBMS_ERRLOG.CREATE_ERROR_LOG (

dml_table_name IN VARCHAR2,

err_log_table_name IN VARCHAR2 := NULL,

err_log_table_owner IN VARCHAR2 := NULL,

err_log_table_space IN VARCHAR2 := NULL,

skip_unsupported IN BOOLEAN := FALSE);

注意：再次执行该包会报错，执行前须确认错误记录表不存在。

dml使用错误日志表

insert into emp1(empno,ename) select empno,ename from emp log errors into t_err_log reject limit unlimited;

注意红色字体部分。Limimt后面可以是具体数字，表示容错行数

语法：

LOG ERRORS [INTO [schema.]table] [(‘simple_expression‘)] [REJECT LIMIT integer|UNLIMITED]

simple_expression：用来标记t_err_log表ora_err_tag$字段信息

Update、merge和delete也可以使用该方法。

更新完检查t_err_log失败记录及错误原因。

Oracle DML容错处理（1）

标签：comm   ignore   body   ora   自动生成   user   example   case   问题