How cool is this

Taking a look at a new 10g Release 2 feature, DML Error Logging.  Also, a stupid thing I saw in the news.

First for the most ridiculous news item of the day.  Astrologer Sues NASA Over Comet Mission.  Hello?  Anyone at home?  What a waste.

Ok, back to the real world.  I’ve found that the most dreaded words someone writing for a magazine can see are “your column is short, do you have 400 words to add”.  Well, that can be as bad as “your column is long, chop 400 words”.  I got the “too short” over the weekend and had to find another thing or two to write about for my next column.  I vaguely recalled that I knew this feature was coming but I forgot to look for it.  In my search for an extra 400 words, I stumbled upon it again.

DML Error Logging, instead of the 100,000 row update/insert/whatever failing because a single row doesn’t quite work out, we can have the 99,999 successful rows go through and have the one bad row logged to a table!  I mean, this isn’t just cool, this is “change my world, rewrite the books, stop the presses, wow, knock me over with a feather”.

And – the best part – it is really quite easy and intuitive.  This is cool.  Here is a quick demonstration.  We’ll just take the SCOTT.EMP table:

ops$tkyte-ORA10GR2> create table emp
  2  as
  3  select * from scott.emp where 1=0;
 
Table created.
 
and using a new supplied package – we’ll generate the error log for it.  You could create this by hand, you could specify the name via this API, you could do lots of stuff, this is the shortest way.  It’ll default everything and just name the error log table ERR$_<tablename> which seems sensible:

ops$tkyte-ORA10GR2> exec dbms_errlog.create_error_log( 'EMP' ); 
PL/SQL procedure successfully completed.

 
ops$tkyte-ORA10GR2> desc err$_emp
 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)
 EMPNO                            VARCHAR2(4000)
 ENAME                            VARCHAR2(4000)
 JOB                              VARCHAR2(4000)
 MGR                              VARCHAR2(4000)
 HIREDATE                         VARCHAR2(4000)
 SAL                              VARCHAR2(4000)
 COMM                             VARCHAR2(4000)
 DEPTNO                           VARCHAR2(4000)
 
There is the error logging table, they picked datatypes that would be “safe” for each column — if you inserted a JOB that was too long, no worries, it has to fit into a VARCHAR2(4000).  EMPNO isn’t really a number in your input? No problem, it’ll fit into a VARCHAR2 as well.  The other columns you see in here are useful too — the ORA-XXXX error number, the text of the error, the ROWID of the offending row(s) from an update or delete, the operating type (I/U/D) and optionally a ‘tag’ you provide as part of your SQL operation.  That tag will be useful to determine what step of your ETL (extract/transform/load) process failed. 

So, let’s try this out, we’ll add some constraints:

ops$tkyte-ORA10GR2> alter table emp add constraint emp_check_sal check(sal > 900);
Table altered.
 
ops$tkyte-ORA10GR2> create trigger emp_trigger
  2  after insert on emp for each row
  3  begin
  4      if ( :new.ename = 'ALLEN' )
  5      then
  6        raise_application_error
  7        (-20024, 'Failed Validation' );
  8      end if;
  9  end;
 10  /
Trigger created.
 
ops$tkyte-ORA10GR2> alter table emp add
  2  constraint emp_pk primary key(empno);
Table altered.

and using just the old fashioned INSERT, we can see what normally would happen:

ops$tkyte-ORA10GR2> insert /*+ APPEND */ into emp
  2  select * from scott.emp;
insert /*+ APPEND */ into emp
       select * from scott.emp
*
ERROR at line 1:
ORA-02290: check constraint 
           (OPS$TKYTE.EMP_CHECK_SAL) violated
 
ops$tkyte-ORA10GR2> select * from emp;
no rows selected
 

A big “nothing” is what happens, the SQL either entirely happens or doesn’t happen at all.  But, add “LOG ERRORS” and check it out:

ops$tkyte-ORA10GR2> insert /*+ APPEND */ into emp
  2  select * from scott.emp
  3  LOG ERRORS REJECT LIMIT UNLIMITED;
12 rows created.
 
Now, since anyone that can spell Oracle knows there are 14 rows in emp – something happened here.  Something good in a way.  We had errors (bad) that were not ignored (good) but logged off to the side (better) and we still took advantage of BULK OPERATIONS (best of all).  We can review the bad records:

ops$tkyte-ORA10GR2> select ORA_ERR_NUMBER$,
  2         ORA_ERR_OPTYP$,
  3         EMPNO,
  4         ORA_ERR_MESG$
  5    from err$_emp;

ORA_ERR_NUMBER$ OR EMPNO ORA_ERR_MESG$
--------------- -- ----- ---------------
           2290 I  7369  ORA-02290: chec
                         k constraint (O
                         PS$TKYTE.EMP_CH
                         ECK_SAL) violat
                         ed
 
          20024 I  7499  ORA-20024: Fail
                         ed Validation
                         ORA-06512: at "
                         OPS$TKYTE.EMP_T
                         RIGGER", line 4
 
                         ORA-04088: erro
                         r during execut
                         ion of trigger
                         'OPS$TKYTE.EMP_
                         TRIGGER'

It even works during UPDATES:

ops$tkyte-ORA10GR2> update emp
  2     set sal = sal - 2000
  3   where sal between 2000 and 3000
  4  LOG ERRORS ('My Update')
  5  REJECT LIMIT UNLIMITED;
 
3 rows updated.
 
But really, we didn’t update three rows.  Note this time I passed in a nice little name, which we can now use against this cumulative error log table (the failed inserts are still in there as well)

ops$tkyte-ORA10GR2> select ORA_ERR_NUMBER$,
  2         ORA_ERR_OPTYP$,
  3             ORA_ERR_TAG$,
  4             ORA_ERR_ROWID$,
  5         EMPNO,
  6         ORA_ERR_MESG$
  7    from err$_emp
  8   where ora_err_tag$ is not null;
 
  ORA OR ORA_ERR_T ORA_ERR_R EMPNO ORA_ERR_MESG$
----- -- --------- --------- ----- ---------------
 2290 U  My Update AAAM04AAE 7698  ORA-02290: chec
                   AAAAGdAAD       k constraint (O
                                   PS$TKYTE.EMP_CH
                                   ECK_SAL) violat
                                   ed
 
 2290 U  My Update AAAM04AAE 7782  ORA-02290: chec
                   AAAAGdAAE       k constraint (O
                                   PS$TKYTE.EMP_CH
                                   ECK_SAL) violat
                                   ed
We have the row data (EMPNO is filled in) as well as the rowid’s of the bad rows.  We can see what caused the error — the check constraint violation, and see how many rows violated the check constraint — two in this case.  Our update would have modified 3 rows had all of the rows been “OK”, but it really only did 1.

This new feature changes everything.  No more excuses in the future, BULK operations are in style, they are cool, they are efficient and you’ll be able to erase a ton of code (which believe it or not is a cool thing, erasing code). 

Can you tell I’m sort of excited about this new feature?