Friday, February 17, 2012

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 ?



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

No comments:

Post a Comment

All about CSS

From book HTML & CSS - Design and Build Websites - Jon Duckett CSS works by associating rules with HTML elements. These rules govern how...