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.

EXECUTE DBMS_ERRLOG.CREATE_ERROR_LOG(‘raises’, ‘errlog’);

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 10.2.0.3 database.

SQL> INSERT INTO raises
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_ERR_MESG$
—————————————————————————————————-
ORA_ERR_TAG$
—————————————————————————————————-
EMP_ID
—————————————————————————————————-
SAL
—————————————————————————————————-
ORA-02290: check constraint (SCOTT.CHECK_SAL) violated
my_bad
7499
1760

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

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

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.

 

Advertisements
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:

WordPress.com Logo

You are commenting using your WordPress.com 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 )

Google+ photo

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

Connecting to %s