Invalid ledml Error Log notes

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 the first

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 the first

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.

,