Systems Engineering and RDBMS

More on the LOG ERRORS clause

Posted by decipherinfosys on August 20, 2007

In one of our previous blog post, we had briefly covered the LOG ERRORS clause that was introduced in Oracle version 10gR2.  In today’s post, we will elaborate more on that feature.  The place where this feature comes into play is when a database developer is performing insert using sub query (bulk insert). One, of course does not want the entire load to fail in case of any data type and/or constraint violation error for a specific record. Instead, it is preferred to log it in some other table and continue inserting other records. Later on, one can re-visit the error table and correct the data and load it back into actual table.

In previous versions of Oracle, it was not possible to filter out erroneous records separately during a bulk insert operation. If any single record fails, Oracle rolls back the entire load and we do not have visibility into the records that needed to be fixed in order to prevent this failure during a bulk insert operation. In 10g Oracle introduced new error_logging clause. This clause allows to capture DML errors and logs the column values of the affected row and inserts them into error table. For detailed syntax of the error_logging clause, refer to Oracle SQL reference manual.

First let us create an empty table. If you already have table with the same name, change all the occurrences of the table name with some other name.

CREATE TABLE Invoice
(
INVOICE_NUMBER      NUMBER(9) NOT NULL,
INVOICE_DATE        DATE NOT NULL,
CLIENT_ID           NUMBER(9)     NOT NULL,
INVOICE_AMT         NUMBER(9,2) DEFAULT 0 NOT NULL,
PAID_FLAG           NUMBER(1) DEFAULT 0 NOT NULL, — 0 Not paid/ 1 paid
CONSTRAINT PK_INVOICE PRIMARY KEY(INVOICE_NUMBER)
)
— TABLESPACE Clause
/

CREATE SEQUENCE INVOICE_SEQ
START WITH 1
CACHE 100
/

CREATE UNIQUE INDEX INVOICE_IND_1 ON INVOICE(CLIENT_ID)
— TABLESPACE CLAUSE
/

In order to use error_logging clause, we need to create first error table to hold the DML errors. Execute DBMS_ERRLOG package from SQL*Plus prompt to create error table. Make sure that you have an execute privilege on DBMS_ERRLOG package.

EXECUTE DBMS_ERRLOG.CREATE_ERROR_LOG(‘INVOICE’,’INVOICE_ERROR’);

INVOICE  is the table in which we will be inserting data. INVOICE_ERROR table will hold the DML error records. One can give any name to hold the errors. Now let us try to insert some data into the Invoice table.  The INVOICE_ERROR table has all the columns of INVOICE table plus some more columns in which Oracle will store error information.

Following is the insert command without error_logginc clause.

INSERT  INTO Invoice(invoice_number,invoice_date,client_id,invoice_amt)
VALUES(invoice_seq.nextval,sysdate,NULL,1100);

Since we are trying to insert null value in not null column (client_id), we are running into following error.

ERROR at line 2:
ORA-01400: cannot insert NULL into (“DECIPHER”.”INVOICE”.”CLIENT_ID”)

Now let us run same statement with error_logging clause and see the behavior. Record will not be inserted into INVOICE table but it will not give any error as well instead error record will get created in error logging table.

INSERT  INTO Invoice(invoice_number,invoice_date,client_id,invoice_amt)
VALUES(invoice_seq.nextval,sysdate,NULL,1100)
LOG ERRORS INTO Invoice_Error(‘TEST‘) REJECT LIMIT 10;

In above statement,
·   ‘log errors into’ is the clause to add error records into INVOICE_ERROR table.
·   ‘TEST’ is an error tag. It identifies all the errors from above statement in error logging table.  Here we will not get any errors but record will be inserted into INVOICE_ERROR table.
·   ‘Reject Limit’ defines the maximum number of errors to allow before statement is terminated. If number of error records is more than the reject limit, then all the changes done by statement will be rolled back. Errors will be logged into error logging table.

Following statement can be issued to examine the content of the INVOICE_ERROR table.

SELECT invoice_number as Inv#,invoice_Date as Inv_Date,
client_id as CID, ora_err_mesg$
FROM invoice_Error
WHERE ora_err_tag$ = ‘TEST’;

Following is the formatted output.

INV#  INV_DATE   CID   ORA_ERR_MESG$
—– ———- —– ————————————————–
2     30-JAN-07        ORA-01400: cannot insert NULL into (“DECIPHER”.”
INVOICE”.”CLIENT_ID”)

Now let us execute insert statement using subquery. In following statement, we will error out record # 3 and  #4. For record #3 invoice date is null and for record #4 client_id exceeds the data length.

INSERT INTO Invoice(invoice_number,invoice_date,client_id,invoice_amt)
SELECT invoice_Seq.nextval,
(CASE WHEN rownum = 3 THEN NULL ELSE sysdate END),
(CASE WHEN rownum = 4 THEN 2223334445 ELSE rownum END),
rownum*1000
FROM USER_TABLES
WHERE rownum < 10
LOG ERRORS INTO INVOICE_ERROR(‘TEST1’) REJECT LIMIT 10;

Out of 9 records, 2 records will error out and rest of them will get inserted. Following is the formatted output from the error log table.

INV#  INV_DATE   CID   ORA_ERR_MESG$
—– ———- —– ————————————————–
5                3     ORA-01400: cannot insert NULL into (“DECIPHER”.”
INVOICE”.”INVOICE_DATE”)
6     30-JAN-07  22233 ORA-01438: value larger than specified precision a
34445 llowed for this column

This feature is very useful in case of large inserts and updates. Just because we have couple of errors, it will not terminate entire load, instead records other than the error records will get inserted successfully.

Limitations of this clause:

In the following conditions, statement will terminate and rollback the changes without logging any errors:

·   Unique constraint or index violation during direct-path INSERT or MERGE.
·   Unique constraint or index violation during UPDATE or MERGE
·   Violated deferred constraints

Let us check the above limitation by using an update statement as an example.  Run following SQL where we are compromising client_id uniqueness. We have a unique index on client_id column.

UPDATE INVOICE
SET CLIENT_ID = 1
WHERE CLIENT_ID = 2
LOG ERRORS INTO INVOICE_ERROR(‘TEST2’) REJECT LIMIT 10;

This statement will give following error without logging error into error logging table.

ERROR at line 1:
ORA-00001: unique constraint (DECIPHER.INVOICE_IND_1) violated

Sorry, the comment form is closed at this time.

 
%d bloggers like this: