One in a million

How many times have you been creating or migrating data and have everything fail because of one stinkin’ record? Can you deal with it? Of course. There are a lot of ways to trap that. Try/Catch with single inserts at a time. Spend time finding the offending record. Etc. When you do bulk inserts from CF, you have to be especially careful if you don’t want your page to fail half way through the operation with half the data saved.

Wouldn’t it be nice if you could basically say “Hey Oracle… I am going to insert a bunch of records. A few might not be valid, but please insert the rest and just let me know which ones failed. Please….”

Well, it turns out you can do just that. If you want to Goolge this, the proper term is DML Error Logging and was part of Oracle 10g Release 2.

Since I prefer to show and then explain…

SQL> CREATE TABLE EXAMPLE_TAB
 2 ( EMP_ID NUMBER,
 3   EMP_NAME VARCHAR2(30),
 4   EMP_POSITION VARCHAR2(30),
 5   CONSTRAINT PK_EXAMPLE_TAB PRIMARY KEY (EMP_ID)
 6 );

Table created.

SQL> INSERT INTO EXAMPLE_TAB (EMP_ID, EMP_NAME, EMP_POSITION)
 2   VALUES (100,'Chris','Standing');

1 row created.

SQL> INSERT INTO EXAMPLE_TAB (EMP_ID, EMP_NAME, EMP_POSITION)
 2   VALUES (110,'Angela','Sitting');

1 row created.

SQL> INSERT INTO EXAMPLE_TAB (EMP_ID, EMP_NAME, EMP_POSITION)
 2   VALUES (120,'Monika','Running');

1 row created.

SQL> INSERT INTO EXAMPLE_TAB (EMP_ID, EMP_NAME, EMP_POSITION)
 2   VALUES (110,'Jon Connor','Crawling');
INSERT INTO EXAMPLE_TAB (EMP_ID, EMP_NAME, EMP_POSITION)
*
ERROR at line 1:
ORA-00001: unique constraint (SNWAENGDRAW.PK_EXAMPLE_TAB) violated
SQL> COMMIT;

Commit complete.

SQL> select count(*) from example_tab;

COUNT(*)
----------
 3

What happened? Three records were created and one failed. No big deal. Running this set of queries, it would be easy to identify the problem and fix it. If you had this in code somewhere, you would need to handle that ugly error message.

Let’s try it again with the error logging.

SQL> CREATE TABLE EXAMPLE_TAB
 2 ( EMP_ID NUMBER,
 3   EMP_NAME VARCHAR2(30),
 4   EMP_POSITION VARCHAR2(30),
 5   CONSTRAINT PK_EXAMPLE_TAB PRIMARY KEY (EMP_ID)
 6 );

Table created.

SQL> EXEC DBMS_ERRLOG.CREATE_ERROR_LOG( 'EXAMPLE_TAB' );

PL/SQL procedure successfully completed.

SQL> INSERT INTO EXAMPLE_TAB (EMP_ID, EMP_NAME, EMP_POSITION)
 2   VALUES (100,'Chris','Standing') LOG ERRORS REJECT LIMIT UNLIMITED;

1 row created.

SQL> INSERT INTO EXAMPLE_TAB (EMP_ID, EMP_NAME, EMP_POSITION)
 2   VALUES (110,'Angela','Sitting') LOG ERRORS REJECT LIMIT UNLIMITED;

1 row created.

SQL> INSERT INTO EXAMPLE_TAB (EMP_ID, EMP_NAME, EMP_POSITION)
 2   VALUES (120,'Monika','Running') LOG ERRORS REJECT LIMIT UNLIMITED;

1 row created.

SQL> INSERT INTO EXAMPLE_TAB (EMP_ID, EMP_NAME, EMP_POSITION)
 2   VALUES (110,'Jon Connor','Crawling') LOG ERRORS REJECT LIMIT UNLIMITED;

0 rows created.

SQL> commit;

Commit complete.

SQL> select count(*) from example_tab;

COUNT(*)
----------
 3

SQL> DESC ERR$_EXAMPLE_TAB;
 Name             Null?             Type
 ---------------------------------------------
 ORA_ERR_NUMBER$                    NUMBER
 ORA_ERR_MESG$                      VARCHAR2(2000)
 ORA_ERR_ROWID$                     ROWID
 ORA_ERR_OPTYP$                     VARCHAR2(2)
 ORA_ERR_TAG$                       VARCHAR2(2000)
 EMP_ID                             VARCHAR2(4000)
 EMP_NAME                           VARCHAR2(4000)
 EMP_POSITION                       VARCHAR2(4000)

SQL> SELECT ORA_ERR_MESG$ FROM ERR$_EXAMPLE_TAB;

ORA_ERR_MESG$
-------------------------------------------------------------------------
ORA-00001: unique constraint (SNWAENGDRAW.PK_EXAMPLE_TAB) violated

Neat. For the sake of brevity, I will talk in bullets about the above code.

• “EXEC DBMS_ERRLOG.CREATE_ERROR_LOG( ‘EXAMPLE_TAB’ );” Is telling Oracle, “Hey… log DML errors for the table EXAMPLE_TAB.
• When you tell Oracle to set up logging, it creates a new table with your origal table name and “ERR%_” prepended to it.
• Adding “LOG ERRORS REJECT LIMIT UNLIMITED” is telling Oracle, “Hey… I think this will work, but if it doesn’t, don’t throw an error. Just log the failure in that table I told you about.” Also… if dealing with a huge amount of data, you might want to think about not setting the limit to UNLIMITED.
• Notice that the insert above that failed returned “0 rows created”.
• If you start logging, have a plan in place to purge the data from the log table. Log tables are notorious for growing faster than you thought they would. At least monitor the size.

This technique works with UPDATES as well. The constraint type is also flexible. I just chose a PK for my example.

When I first learned of this technique, my world shifted a little. I began going back over the hours and hours I spent trying to track down malformed records in bulk inserts/updates. Give this technique a try and keep it in your library. It isn’t appropriate for everything, but when it is… it can save you A LOT of time.

For a much better explanation of what you can do with the error logging:

http://tkyte.blogspot.com/2005/07/how-cool-is-this.html

Advertisements