Page: 1 2 3

Like every new release, Oracle 10g Release 2 also came up with a number of long awaited features. Today we will discuss a pretty nifty feature for Oracle developers – The LOG ERRORS clause.

Loading data in bulk:

In general, the most efficient way to load millions of records into a table is to use INSERT, UPDATE, or MERGE statements to process your data in bulk. Consider a scenario where you use INSERT command to load 10,000 records in your table. Everything is going smooth but after 5000 records you get a unique constraint error. In such scenario Oracle will rollback all the records that have been inserted till that point of time and will give an error.

LOG ERRORS clause:

The LOG ERRORS clause is a great feature that is applicable to all Oracle DML statements. With this feature you will be able to log only the failed records rather than failing the entire bulk load itself. It enables you to insert all other records and only the records that result in error will be skipped.

Insert /*+ APPEND */ into MYTABLE (myCol1, myCol2, myCol3)
  select a1, a2, a3 from MYTABLE2
  LOG ERRORS into ERRLOG ('MYTABLE_LOG')
  REJECT LIMIT UNLIMITED;

Now our failed records will log into table MYTABLE_LOG. Along with failed records, the Oracle error number, the text of the error message, the type of the DML operation and ROWID values (for Update/Delete statement) will also be stored. Now if your single record is failed due to bad data condition then your entire load will not fail. Remember the performance is not compromised at all and still you can perform the SET based operations.

Bulk Inserts:

The LOG ERRORS clause is especially important in bulk inserts (insert using sub query). Obviously we don't want entire load to fail so it is better to log failed records it in some other table and continue inserting other records. You can then retrieve values from error table and load back correct data into actual table. Let us see the beauty of this feature with below example.

Create Table:

First of all we will create an empty table and create sequence and unique index for it.

CREATE TABLE MYINVOICE
(
MYINVOICE_NUM      NUMBER(9) NOT NULL,
DATE_INVOICE        DATE NOT NULL,
CUSTOMERID           NUMBER(9)     NOT NULL,
AMOUNT         NUMBER(9,2) DEFAULT 0 NOT NULL,
IS_PAID           NUMBER(1) DEFAULT 0 NOT NULL, — 0 Not paid/ 1 paid
CONSTRAINT PK_MYINVOICE PRIMARY KEY(MYINVOICE_NUM)
)
/

CREATE SEQUENCE MYSEQ
START WITH 1
CACHE 100
/

CREATE UNIQUE INDEX MYINDEX ON MYINVOICE(CUSTOMERID)
/ Continued...

Page: 1 2 3

 More Oracle Articles, Database Articles and DBA Tips
   Database Security: Step by step guideline
   A Guide to Oracle Bitmap Index Techniques!!
   Efficient Oracle Migration, Amazing Tricks!
   Data Warehouse Testing: The key to Data Warehouse success
   Is Relational DBMS Dying?