Oracle DML Error Log (Note), oracledml

Oracle DML Error Log (Note), oracledml

DML error log is an error log function introduced by oracle10gR2 similar to SQL * Loader. The basic principle is to transfer any records that may cause statement failure to an error log table.

The specific usage is as follows:

1. Use DBMS_ERRLOG.CREATE_ERROR_LOG to create an error log table.

-- Original table my_test table structure desc my_test Name Type Nullable Default Comments ------ ------------ ------- -------- sid number name VARCHAR2 (20) y gender integer y age number y Hober VARCHAR2 (20) Y -- execute the following statement to generate the error log table execute dbms_errlog.create_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 Default Comments --------------- -------------- -------- ------- -------- ORA_ERR_NUMBER $ number y limit $ VARCHAR2 (2000) Y limit $ UROWID (4000) Y ORA_ERR_OPTYP $ VARCHAR2 (2) Y ORA_ERR_TAG $ VARCHAR2 (2000) y sid VARCHAR2 (4000) y name VARCHAR2 (4000) y gender VARCHAR2 (4000) y age VARCHAR2 (4000) Y Hober VARCHAR2 (4000) Y

The ORA_ERR_TAG $ field can store user-defined data.

2. Use the my_test_bad error log table in the insert statement.

Insert into my_test (sid, name, gender, age, holobby) values (12, 'joy ', 2, 'age', 'book, football, run ') log errors into my_test_bad; insert into my_test (sid, name, gender, age, holobby) values (12, 'joy ', 2, 'age', 'book, football, run ') log errors into my_test_bad ORA-01722: Invalid Number SQL> select * from my_test_bad; -- (the results are not standard, as shown in the figure)

　　

The update, delete, and merge statements can all be followed by error logs in the same way. For example:

  update my_test     set age = 'yyy'   where sid = 12    log errors into my_test_bad;

　　

Note: The log errors clause does not cause implicit commit. That is to say, even if a rollback error occurs, the error information is stored in the error log table.