Invalid ledml Error Log notes

Source: Internet
Author: User
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.

,

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.