Oracle DML error Log

The DML error log is a sql*loader-like error logging feature introduced by ORACLE10GR2. The rationale for this is to transfer any records that might cause the statement to fail and put them in an error log table.

The specific use is as follows:

1, use Dbms_errlog. Create_error_log to create the error log table

--Original Table MY_TEST table structure desc my_test Name Type Nullable Default Comments-----------------------------------------SI                         D number NAME VARCHAR2 y GENDER INTEGER y Age number y HOBBY VARCHAR2 y-executes the following statement, generating the error log table execute DBMS_ERRLOG.C Reate_error_log (' my_test ', ' My_test_bad '); PL/SQL procedure successfully completed.--generated error log table My_test_bad structure desc my_test_badname Type Nullable Def                         Ault Comments----------------------------------------------------ora_err_number$ number Y ora_err_mesg$ VARCHAR2 (x) y ora_err_rowid$ urowid (4000) Y OR             a_err_optyp$ VARCHAR2 (2) y ora_err_tag$ VARCHAR2 (+) y SID                         VARCHAR2 (4000) y NAME VARCHAR2 (4000) yGENDER VARCHAR2 (4000) y Age VARCHAR2 (4000) Y HOBBY                          VARCHAR2 (4000) Y

Where ora_err_tag$ This field can hold user-defined data

2. Using the My_test_bad error log table in INSERT statements

INSERT INTO My_test  (sid,name,gender,age,hobby)  values (+, ' joy ', 2, ' age ', ' book,football,run ')  log Errors into My_test_bad; Insert into My_test (Sid,name,gender,age,hobby) VALUES (+, ' joy ', 2, ' age ', ' book,football,run ') logs errors into My_test_ Bad ORA-01722: Invalid number sql> select * from my_test_bad;--(results not standardized, shown in figure)  

　　

The Update,delete,merge statement can declare the error log later, in the same way. Such as:

  Update My_test     Set age = ' yyy '   where sid = a    log errors into My_test_bad;

　　

Note: The Log errors clause does not cause an implicit commit, that is, the error message is persisted in the error log table even if a rollback is made.

Oracle DML error Log