Oracle 11g uses DML Error Logging to avoid bulk insert failure

Oracle 11g uses DML Error Logging to avoid bulk insert failure

Use DML Error Logging to avoid bulk insert failure
If an error occurs when an insert statement with a subquery is used to load data. the system terminates the statement and rolls back the entire operation. this operation consumes a lot of time and resources. if the insert statement is used, DML Error Logging can be used to avoid this situation.
 
To use the DML error logging function, you need to add a clause to specify the error logging table name to record the error records encountered during the DML operation.
When an error logging clause is added to a statement, a specific type of error will not be terminated or rolled back. on the contrary, each error record is recorded in the table and the operation statement can continue to be executed. after the operation is complete, you can modify the error records.
 
DML error logging can be used with insert, update, merge, and delete statements.

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

Installing Oracle 12C in Linux-6-64

Install Oracle 11gR2 (x64) in CentOS 6.4)

Steps for installing Oracle 11gR2 in vmwarevm

Install Oracle 11g XE R2 In Debian

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

To use the DML error logging function to insert data
1. Create an error logging table
You can manually create or use the dbms_errlog package to automatically create one.

2. Execute an insert statement containing the error logging clause:
You can select to reference the self-created error logging table. If no error logging table name is provided, the database uses a default error logging table.
The default error loggin table name is err $ _ followed by the first 25 characters inserted into the table name.

. You can choose to include a tag (a number or a string with parentheses). It will be added to the error log to help you identify which statement produces the error. If the tag is ignored, null is used.

. You can select the include reject limit clause.
This clause indicates the maximum number of errors that can be encountered before the insert statement ends and rolls back. It can be specified as unlimited. The default reject limit is 0, which means
When the first error is encountered, the system records the error and rolls back the statement. The parallel operation reject limit is applied to every parallel server process.

3. query the error logging table and modify the rows that generate the error.

Error logging table format
An error logging table consists of two parts:
A set of mandatory columns with incorrect descriptions. For example, a column contains the oracle error code.

. Contains a set of optional columns that result in incorrect row data. These column names match the column names in the table for which the insert operation is performed.
The number of columns in the error logging table can be 0, 1 or more until they are equal to the number of columns in the DML table. If the columns in the error logging table are the same as those in the DML table
With the same name, the data in the row records that violate the insert operation will be written into the columns in the error logging table. if the columns in the DML table are not related to the error logging table, this column will not be logged. if the error logging table contains a column that does not match the DML table, the column is ignored.
 
The type conversion error is a possible error type. The data type of the optional columns in the error logging table must be a data type that can capture any value without data loss or conversion errors. (if the optional log column and DML table column have the same data type, the same data conversion problem occurs when the problematic data is captured ). the database makes the best effort to record the useful information of data that may cause conversion errors. if the value cannot be derived, the null value is recorded for this column. if an error is inserted into the errog logging table, the statement is terminated.
 
Table: Forced error description Column
Bytes --------------------------------------------------------------------------------------------------------------
Column name data type description
Bytes --------------------------------------------------------------------------------------------------------------
Ora_err_number $ number oracle error code
Ora_err_mesg $ varchar2 (2000) oracle error message text
Ora_err_rowid $ rowid Of The Wrong row (for update and delete)
Ora_err_optyp $ varchar2 (2) Operation Type: insert (I), update (u), delete (d)
Note: errors in the update and insert clauses of merge operations
Distinguish between U and I
Ora_err_tag $ varchar2 (2000) label value provided to the error logging clause
Bytes --------------------------------------------------------------------------------------------------------------
 
Table: column data type of the error log table
Bytes --------------------------------------------------------------------------------------------------------------
DML table column type error log table column type note
Bytes --------------------------------------------------------------------------------------------------------------
Number varchar2 (4000) can record conversion errors
Char/varchar2 (n) varchar2 (4000) Record No information loss value
Nchar/nvarchar2 (n) nvarchar2 (4000) records no information loss value
Date/timestamp varchar2 (4000) Record No information loss value. Use the default date/time format to convert
Character format
Raw (2000) records no value for information loss
Rowid urowid record any type of rowid
Long/lob is not supported
User-Defined data types are not supported
Bytes --------------------------------------------------------------------------------------------------------------
 
Create error log table
You can manually create an error log table or use a pl/SQL package to automatically create an error log table.

You can use the dbms_errlog package to automatically create an error log table. The create_error_log process will create a column with all Mandatory descriptive errors and add all columns in the DML table.
An error log table
First, create a table test_emp to store data.
SQL> create table test_emp as select * from hr. employees where 1 = 2;

Table created.

SQL> alter table test_emp add primary key (employee_id );

Table altered.
 
Insert a record to the test_temp table first because the subsequent insert operation produces an error that violates the primary key constraint.
SQL> insert into test_emp select * from hr. employees where rownum <2;

1 row created.

SQL> commit;
 
Create error log table
SQL> execute dbms_errlog.create_error_log ('test _ emp', 'err _ emp ');

PL/SQL procedure successfully completed.

SQL> desc err_emp
Name Null? Type
-----------------------------------------------------------------------------
ORA_ERR_NUMBER $ NUMBER
ORA_ERR_MESG $ VARCHAR2 (2000)
ORA_ERR_ROWID $ ROWID
ORA_ERR_OPTYP $ VARCHAR2 (2)
ORA_ERR_TAG $ VARCHAR2 (2000)
EMPLOYEE_ID VARCHAR2 (4000)
FIRST_NAME VARCHAR2 (4000)
LAST_NAME VARCHAR2 (4000)
EMAIL VARCHAR2 (4000)
PHONE_NUMBER VARCHAR2 (4000)
HIRE_DATE VARCHAR2 (4000)
JOB_ID VARCHAR2 (4000)
SALARY VARCHAR2 (4000)
COMMISSION_PCT VARCHAR2 (4000)
MANAGER_ID VARCHAR2 (4000)
DEPARTMENT_ID VARCHAR2 (4000)
 
Insert
SQL> insert into test_emp select * from hr. employees log errors into err_emp ('test _ load1') reject limit 40;

106 rows created.

SQL> insert into test_emp select * from hr. employees log errors into err_emp ('test _ load1') reject limit 1000;

0 rows created.

SQL> commit;

Commit complete.

For more details, please continue to read the highlights on the next page:

1 2 Next Page