一、创建表

SQL> create table dmlel
  2>   (pkey varchar2(100) primary key, field1 varchar2(1), field2 varchar2(10) not null);

Table created.

二、循环插入数据，故意有插入不了的数据

declare
i number;
begin
  i := 0;
  while i <= 10 loop
    insert into dmlel (pkey, field1, field2)
    values (i, i, i);
    i := i+1;
  end loop;
end;
/

三、创建错误日志表、有两种方法。

1、

begin
  dbms_errlog.create_error_log('DMLEL','ERROR_LOG_DMLEL') ;
end;
/

desc error_log_dmlel

desc error_log_dmlel
名称                             空值       类型                                                                                                                                                                                           
------------------------------ ---------- ----------------------------------------------
ORA_ERR_NUMBER$                            NUMBER                                                                                                                                                                                       
ORA_ERR_MESG$                              VARCHAR2(2000)                                                                                                                                                                               
ORA_ERR_ROWID$                             UROWID()                                                                                                                                                                                     
ORA_ERR_OPTYP$                             VARCHAR2(2)                                                                                                                                                                                  
ORA_ERR_TAG$                               VARCHAR2(2000)                                                                                                                                                                               
PKEY                                       VARCHAR2(4000)                                                                                                                                                                               
FIELD1                                     VARCHAR2(4000)                                                                                                                                                                               
FIELD2                                     VARCHAR2(4000)                                                                                                                                                                               

8 rows selected


-------
2、

SQL> create table error_log_dmlel_2
  2  (ora_err_number$ number,
  3   ora_err_mesg$ varchar2(2000),
  4   ora_err_rowid$ rowid,  
  5   ora_err_optyp$ varchar2(2),
  6   ora_err_tag$ varchar2(2000) );

Table created.


四、利用错误日志表记录错误

declare i number;
begin
  i := 0;
  while i <= 10 loop
    insert into dmlel (pkey, field1, field2)
    values (i, i, i)
    LOG ERRORS INTO ERROR_LOG_DMLEL REJECT LIMIT 1;
    i := i+1;
  end loop;
end;
/


五、查看错误日志

SQL>set lines 110
SQL> col num$ for 9999999 
SQL> col ora_err_mesg$ for a50
SQL> col ora_err_rowid$ for a25
SQL> col typ for a3
SQL> col pkey for a4
SQL> col field1 for a4
SQL> select ora_err_number$ num$, ora_err_mesg$, ora_err_rowid$, ora_err_optyp$ typ, pkey, field1
  2  from error_log_dmlel
SQL> /

      NUM$ ORA_ERR_MESG$                                      ORA_ERR_ROWID$            TYP PKEY FIEL
---------- -------------------------------------------------- ------------------------- --- ---- ----
     12899 ORA-12899: value too large for column "BULKLOAD"."                           I   10   10 
           DMLEL"."FIELD1" (actual: 2, maximum: 1)

 

 


六、

SQL> rollback ;

Rollback complete.

SQL> select ora_err_number$ num$, ora_err_mesg$, ora_err_rowid$, ora_err_optyp$ typ, pkey, field1
  2  from error_log_dmlel
SQL> /

      NUM$ ORA_ERR_MESG$                                      ORA_ERR_ROWID$            TYP PKEY FIEL
---------- -------------------------------------------------- ------------------------- --- ---- ----
     12899 ORA-12899: value too large for column "BULKLOAD"."                           I   10   10 
           DMLEL"."FIELD1" (actual: 2, maximum: 1)