insert into select批量加载出错解决方案(二)
d for this column
1438 ORA-01438: value larger than specified p I daily_load 10004 name10004
recision allowed for this column
一共5条无法加载的数据，完整记录了下来
[sql]
insert into target_t select * from source_t LOGERRORS INTO target_err_log ('daily_load2') REJECT LIMIT 5;
9999 rows created.
dexter@ORCL> select count(*) from target_t ;
COUNT(*)
----------
9999
支持rollback
dexter@ORCL> rollback ;
Rollback complete.
dexter@ORCL> select count(*) from target_t ;
COUNT(*)
----------
0
dexter@ORCL> insert into target_t select *from source_t LOG ERRORS INTO target_err_log ('daily_load2') REJECT LIMIT 5;
9999 rows created.
dexter@ORCL> commit ;
Commit complete.
dexter@ORCL> select count(*) from target_t ;
COUNT(*)
----------
9999
dexter@ORCL> select count(*) from target_t ;
COUNT(*)
----------
15
第三步修改数据
这里我就不多说。
dml error logging 还支持UPDATE, MERGE,DELETE语句。
附录
日志表中各字段解读
Column Name Data Type Description
ORA_ERR_NUMBER$ NUMBER Oracle error number
ORA_ERR_MESG$ VARCHAR2(2000) Oracle errormessage text
ORA_ERR_ROWID$ ROWID Rowid of the row inerror (for update and delete)
ORA_ERR_OPTYP$ VARCHAR2(2) Type ofoperation: insert (I), update (U), delete (D)
Note: Errors from the update clause and
insert clause of a MERGEoperation are
distinguished by the Uand Ivalues.
ORA_ERR_TAG$ VARCHAR2(2000) Value of thetag supplied by the user in the error logging clause