Error logs recorded during DML Process

Error logs recorded during DML Process

When you insert millions of data records, the insertion fails due to several dirty data records. Oracle 10g R2 has a new feature that inserts records that failed to be inserted into another table.

SQL> drop table test purge;

SQL> drop table test_bad purge;
SQL> create table test as select * from dba_objects where 1 <> 1;

SQL> execute dbms_errlog.create_error_log ('test', 'test _ bad ');

Create table TEST
(
OWNER VARCHAR2 (30 ),
OBJECT_NAME VARCHAR2 (128 ),
SUBOBJECT_NAME VARCHAR2 (30 ),
OBJECT_ID NUMBER,
DATA_OBJECT_ID NUMBER,
OBJECT_TYPE VARCHAR2 (19 ),
Created date,
LAST_DDL_TIME DATE,
TIMESTAMP VARCHAR2 (19 ),
STATUS VARCHAR2 (7 ),
TEMPORARY VARCHAR2 (1 ),
GENERATED VARCHAR2 (1 ),
SECONDARY VARCHAR2 (1 ),
Namespace number,
EDITION_NAME VARCHAR2 (30)
);

Create table TEST_BAD
(
ORA_ERR_NUMBER $ NUMBER,
ORA_ERR_MESG $ VARCHAR2 (2000 ),
ORA_ERR_ROWID $ UROWID (4000 ),
ORA_ERR_OPTYP $ VARCHAR2 (2 ),
ORA_ERR_TAG $ VARCHAR2 (2000 ),
OWNER VARCHAR2 (4000 ),
OBJECT_NAME VARCHAR2 (4000 ),
SUBOBJECT_NAME VARCHAR2 (4000 ),
OBJECT_ID VARCHAR2 (4000 ),
DATA_OBJECT_ID VARCHAR2 (4000 ),
OBJECT_TYPE VARCHAR2 (4000 ),
CREATED VARCHAR2 (4000 ),
LAST_DDL_TIME VARCHAR2 (4000 ),
TIMESTAMP VARCHAR2 (4000 ),
STATUS VARCHAR2 (4000 ),
TEMPORARY VARCHAR2 (4000 ),
GENERATED VARCHAR2 (4000 ),
SECONDARY VARCHAR2 (4000 ),
NAMESPACE VARCHAR2 (4000 ),
EDITION_NAME VARCHAR2 (4000)
);

SQL> insert into test (owner) values (lpad ('1', 31, 'A '))
Log errors into test_bad;
Insert into test (owner) values (lpad ('1', 31, 'A '))
*
Row 3 has an error:
ORA-12899: the value of column "LCAM_TEST". "TEST". "OWNER" is too large (actual value: 31, maximum value: 30)

SQL> insert into test (owner) values (lpad ('2', 30, 'bb '))
Log errors into test_bad;
One row has been created.
SQL> col ORA_ERR_NUMBER format a8;
SQL> col ORA_ERR_MESG $ format a50;
SQL> col OWNER format a20;
SQL> select to_char (ORA_ERR_NUMBER $) as ORA_ERR_NUMBER, ORA_ERR_MESG $, OWNER from test_bad;
ORA_ERR _ ORA_ERR_MESG $ OWNER
------------------------------------------------------------------------------
12899 ORA-12899: the value of column "LCAM_TEST". "TEST". "OWNER" is too large AAAAAAAAAAAAAAAAAA
(Actual value: 31, maximum value: 30) aaaaaaaa1
SQL> select count (1) from test;

COUNT (1)
----------
1

SQL> drop table test1 purge;
SQL> drop table test_bad1 purge;
SQL> create table test1 as select * from dba_objects where 1 <> 1;
SQL> alter table test1 modify object_id number (2 );
SQL> execute dbms_errlog.create_error_log ('test1', 'test _ bad1 ');
SQL> insert into test1 select * from dba_objects
Log errors into test_bad1;
Insert into test1 select * from dba_objects
*
Row 3 has an error:
ORA-01438: value greater than the allowable precision specified for this column
SQL> select count (1) from test1;
COUNT (1)
----------
0
SQL> select count (1) from dba_objects where length (object_id) <= 2 or object_id is null;
COUNT (1)
----------

106

-- As you can see, the preceding statement is rolled back when an error occurs. You need to write the following statements:

SQL> insert into test1 select * from dba_objects
Log errors into test_bad1
Reject limit unlimited;
You have created 106 rows.
SQL> select count (1) from test1;
COUNT (1)
----------
106
SQL> select count (1) from dba_objects where length (object_id) <= 2 or object_id is null;
COUNT (1)
----------

106

Note:

1. The log inserted into the error is an autonomous transaction and will not affect the primary transaction.
2. Using log error does not invalidate append, but writing error does not use direct path insertion.
3. An update operation that violates the unique key or constraint will cause a rollback failure.

Oracle DML Process

PL/SQL ORA-14551: cannot perform DML operations in queries solved

Common MySQL DDL, DML, and DCL languages (example)

Execute batch DML exercises for Oracle basic transactions and ForAll

Oracle DML Statement (insert, update, delete) rollback Estimation