handling exception for insert into select from

SCHEMA 1

I have table transaction table

create table TXN_HEADER
(
  txn_id         NUMBER(10) not null,
  txn_Date      date 
  product_id    NUMBER(10),
  company_id    NUMBER(10),
  dealer_id     NUMBER(10),
  tran_amt      number(10,2)
)

The above table having foreign key references to product.product_id and company.company_id.

This table having 5m rows

SCHEMA 2

create table TXN_HEADER_REPORTS
(
  txn_id         NUMBER(10) not null,
  txn_Date      date 
  product_id    NUMBER(10),
  company_id    NUMBER(10),
  dealer_id     NUMBER(10),
  tran_amt      number(10,2)
)

here also we have the same constraints , having foreign key references to product.product_id and company.company_id.

in schema 2 we are trying to insert all the rows from schemea 1 to schema 2 in one shot, like this

begin 
  insert into TXN_HEADER_REPORTS (
  txn_id, txn_Date ,product_id,company_id   ,  dealer_id ,  tran_amt)
  select 
  txn_id, txn_Date ,product_id,company_id   ,  dealer_id ,  tran_amt
  from schema1.TXN_HEADER;
  commit;
exception   
  when others then 
    < ... procedure to log the errors > 
end;

now we are trying to execute the above procedure , and it failed due to foreign key constraint of one rows. But entire my transaction rollback. Actually i dont want to use cursor to process the rows one by one , at it takes long time. So i used to "insert into .. SElect from " but due to constraints of 1 row all my transaction not moved to schema2.txn_Extract_hdr.

Is there any way to trap only that failed and to process the other rows without terminating

Please advice ..

Solution

You can create an error log table, and then use a single insert:

exec dbms_errlog.create_error_log(dml_table_name => 'TXN_HEADER_REPORTS');

insert into TXN_HEADER_REPORTS ( txn_id, txn_Date ,product_id,company_id ,
  dealer_id , tran_amt)
select txn_id, txn_Date ,product_id,company_id , dealer_id , tran_amt
from schema1.TXN_HEADER
log errors into ERR$_TXN_HEADER_REPORTS reject limit unlimited;

Any rows that can't inserted will be recorded in the ERR table. Note that this is plain SQL, it doesn't need to be in a PL/SQL block.