oracle实现对表dml错误记录日志

   DBMS_ERRLOG提供了一个存储过程CREATE_ERROR_LOG，这个存储过程创建一个错误日志表，当DML出错时，操作不会终止和回滚，而是将相关的错误写到错误日志表中。
   CREATE_ERROR_LOG不支持一些数据类型：LONG, CLOB,BLOB, BFILE, and ADT。
   
    简单示例：

SQL> create unique index ind_test00_a on test00(a);

Index created.

SQL> EXEC DBMS_ERRLOG.CREATE_ERROR_LOG('TEST00','TEST00_ERROR_LOG');

PL/SQL procedure successfully completed.

SQL> desc test00
 Name                                                                    Null?   Type
 -------------------------------------------------------------------------------- -------------------------------------------------
 A                                                                                NUMBER(10)
 B                                                                                NUMBER(10)

SQL> desc test00_error_log
 Name                                                                    Null?   Type
 -------------------------------------------------------------------------------- -------------------------------------------------
 ORA_ERR_NUMBER$                                                                  NUMBER
 ORA_ERR_MESG$                                                                    VARCHAR2(2000)
 ORA_ERR_ROWID$                                                                   ROWID
 ORA_ERR_OPTYP$                                                                   VARCHAR2(2)
 ORA_ERR_TAG$                                                                     VARCHAR2(2000)
 A                                                                                VARCHAR2(4000)
 B                                                                                VARCHAR2(4000)
SQL> INSERT INTO TEST00 values(1,1) LOG ERRORS INTOTEST00_ERROR_LOG REJECT LIMIT 1;

1 row created.

SQL> INSERT INTO TEST00 values(2,2) LOG ERRORS INTOTEST00_ERROR_LOG REJECT LIMIT 1;

1 row created.

SQL> commit;

Commit complete.

SQL> select * from test00;

        A         B
---------- ----------
        1         1
        2         2

SQL> select * from test00_error_log;

no rows selected

SQL> INSERT INTO TEST00 values(2,2) LOG ERRORS INTOTEST00_ERROR_LOG REJECT LIMIT 1;
---插入重复数据，未报错，操作正常进行。

0 rows created.

SQL>commit;    

Commit complete.

SQL> select * from test00;  ---数据未插入到表中，结果是正确的

        A         B
---------- ----------
        1         1
        2         2

SQL> select * from test00_error_log; ---错误操作记录到了错误日志表中

ORA_ERR_NUMBER$ORA_ERR_MESG$                    ORA_ERR_ROWID$                   ORA_ERR_OPTYP$        ORA_ERR_TAG$          A         B
--------------- ------------------------------------------------------------------ -------------------------------------------- ---------- ----------
             1 ORA-00001: unique constraint(WXL                                  I                                            2         2
               UN.IND_TEST00_A) violated


SQL>

附录：
CREATE_ERROR_LOG ProcedureParameters
CREATE_ERROR_LOG参数：

Parameter	Description
dml_table_name	The name of the DML table to basethe error logging table on. The name can be fully qualified (forexample, emp, scott.emp,"EMP", "SCOTT"."EMP"). If a namecomponent is enclosed in double quotes, it will not be uppercased.
err_log_table_name	The name of the error loggingtable you will create. The default is the first 25characters 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 theerror logging table. You can specify the owner indml_table_name. Otherwise, the schema of the currentconnected user is used.
err_log_table_space	The tablespace the error loggingtable will be created in. If not specified, the default tablespacefor the user owning the DML error logging table will beused.
skip_unsupported	When set to TRUE,column types that are not supported by error logging will beskipped over and not added to the error logging table. When set to FALSE,an unsupported column type will cause the procedure toterminate. The default isFALSE.