一、创建表
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)
转载于:https://blog.51cto.com/lbxwang/1107750