INSERT and Error Logging

I have come up to this feature of Oracle lately, when I was working on a certain script to insert and merge data from various tables into one, and was looking out for an effective way to record the errors which I get during my INSERT statements. I had already implemented it using a separate procedure, which I could call at any time and it would log the errors, as I needed them, in a separate table. However this way below looks to be more cooler than the usual ones.

Firstly we need to create a table which would hold the errors. This table is created with a name and the name of the table whose errors would be captured. So, I need to create my target table first, where I would insert data.

Note: For obvious reasons, I am using a plain example, and not the code which I was working on.

Here’s the code used for creating the target table:

CREATE TABLE raises (emp_id NUMBER, sal NUMBER CONSTRAINT check_sal CHECK(sal > 8000));

Now I can create the table which would log the errors.


As per the above statement, all errors which are received during any/all INSERT Statements on the ‘raises’ table would be recorded into the ‘errlog’ table. Below is the test run to see if it works:

Note: I am logged on to the ‘SCOTT’ account on my database.

2 SELECT empno, sal*1.1 FROM emp
3 WHERE comm > .2
4 LOG ERRORS INTO errlog (‘my_bad’) REJECT LIMIT 10;

0 rows created.

The reason why 0 rows were created is because the constraint which was created during the create table statement was violated. Let us now check the error log table.

SQL> SELECT ORA_ERR_MESG$, ORA_ERR_TAG$, emp_id, sal FROM errlog;

ORA-02290: check constraint (SCOTT.CHECK_SAL) violated

ORA-02290: check constraint (SCOTT.CHECK_SAL) violated

ORA-02290: check constraint (SCOTT.CHECK_SAL) violated

As we see above, the rows, or the values in the EMP table to be specific, for which the errors are seen, are captured into the log table.

As per Oracle 10.2 documentation:

The DBMS_ERRLOG package provides a procedure that enables you to create an error logging table so that DML operations can continue after encountering errors rather than abort and roll back. This enables you to save time and system resources.

More details on the above procedure can be found here.

I would try this with UPDATE statements as well, but as the documentation says, it should work on any DML statement.


This entry was posted in database, Oracle, Oracle DBA, programming. Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s