Recent Posts
Categories
Advertisements
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.
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
Recent Comments