用dbms_error記錄load時的錯誤

當我們向一個表中load(insert)資料時,如果遇到錯誤(如,違反了PK),這時語句則會終止並rollback所有.這樣既浪費了時間又佔了系統資源.那麼有沒有辦法,當遇到錯誤時不讓語句終止,讓其跳過錯誤繼續執行並將其錯誤記錄下來呢?

答案是肯定的,下面請看測試案例.


YXYUP@dbatest>desc t_test;
 Name                                                                     Null?    Type
 ------------------------------------------------------------------------ -------- -------------------------------------------------
 ID                                                                       NOT NULL NUMBER


YXYUP@dbatest>alter table t_test drop column name;

Table altered.

YXYUP@dbatest>begin
  2  for i in 1..10
  3  loop
  4  execute immediate 'insert into t_test values(:x)' using i;
  5  end loop;
  6  insert into t_test values(1);
  7  commit;
  8  end;
  9  /
begin
*
ERROR at line 1:
ORA-00001: unique constraint (YXYUP.PK_ID) violated
ORA-06512: at line 6


YXYUP@dbatest>exec dbms_errlog.create_error_log('T_TEST') ;

PL/SQL procedure successfully completed.


---用dbms_errlog.create_error_log建立用於記錄錯誤的表,表名預設是ERR$_加上要操作的表,如下.

YXYUP@dbatest>desc err$_t_test;
 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)
 ID                                                                                VARCHAR2(4000)

YXYUP@dbatest>select * from err$_t_test;

no rows selected




YXYUP@dbatest>begin
  2  for i in 1..10
  3  loop
  4  execute immediate 'insert into t_test values(:x)' using i;
  5  end loop;
  6  insert into t_test values(1);
  7  commit;
  8  end;
  9  /
begin
*
ERROR at line 1:
ORA-00001: unique constraint (YXYUP.PK_ID) violated
ORA-06512: at line 6


YXYUP@dbatest>begin
  2  for i in 1..10
  3  loop
  4  execute immediate 'insert into t_test values(:x)' using i;
  5  end loop;
  6  insert into t_test values(1)
  7  LOG ERRORS
  8  REJECT LIMIT UNLIMITED;
  9  commit;
 10  end;
 11  /

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.04

YXYUP@dbatest>select * from t_test;

        ID
----------
         1
         2
         3
         4
         5
         6
         7
         8
         9
        10

10 rows selected.



YXYUP@dbatest> select  ORA_ERR_NUMBER$,ORA_ERR_MESG$,ORA_ERR_OPTYP$ ,ID from  err$_t_test

ORA_ERR_NUMBER$ ORA_ERR_MESG$                                                ORA_ERR_OPTYP$       ID
--------------- ------------------------------------------------------------ -------------------- ----------
              1 ORA-00001: unique constraint (YXYUP.PK_ID) violated          I                    1

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/7364032/viewspace-611737/，如需轉載，請註明出處，否則將追究法律責任。