Saving an error while DML statement...

It was an interview times, as our project went to tcs... every one was either facing some interview or preparing for the same.

Birwatkar was facing interview at CnC, and below was one of the question asked to him..

How to log errors while performing DML Operations through sql ?

Below code I got from http://www.oracle-base.com/articles/10g/DmlErrorLogging_10gR2.php. This is really useful functionality given by Oracle and saves lots of debugging time.

1. First create source and target table

create table insertTestSourec

(

    id varchar(200)   

)

create table insertTestTarget

(id number

)

2. Create error log table using proc create_error_log

BEGIN

  DBMS_ERRLOG.create_error_log (dml_table_name => 'insertTestTarget');

END;

insert into insertTestSourec values(1)

insert into insertTestSourec values(2)

insert into insertTestSourec values('t')

insert into insertTestTarget

select * from insertTestSourec

Here, statement will get rollback and not a single record will get inserted into target table.

Check your error log table using below sql

select *

from all_tables

where table_name like upper('%insertTestTarget%')

3. Verify the error log table

insert into insertTestTarget

select * from insertTestSourec

LOG ERRORS INTO ERR$_INSERTTESTTARGET ('INSERT') REJECT LIMIT UNLIMITED;

4. Here, after execution of above statement, 2 records (1 and 2) will get inserted into target table and incorrect record will get inserted into ERR$_INSERTTESTTARGET.

select *

from ERR$_INSERTTESTTARGET