Oracle DML Error Log notes

Oracle DML Error Log notes

DML error log is an error log function introduced by Oracle 10gR2 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.

-- My_test table structure of the original table

Desc my_test

Name Type Nullable Default Comments

-----------------------------------------

SID NUMBER

NAME VARCHAR2 (20) Y

GENDER INTEGER Y

AGE NUMBER Y

Holobby VARCHAR2 (20) Y

 

 

-- Execute the following statement to generate an 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_bad

Name Type Nullable Default Comments

----------------------------------------------------

ORA_ERR_NUMBER $ NUMBER Y

ORA_ERR_MESG $ VARCHAR2 (2000) Y

ORA_ERR_ROWID $ 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

Holobby 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 standardized and are displayed in graphs)

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.