Dear Experts,
PEOPLE – Souce table from which data is to be read
CUSTOMER AND MERCHANT– Target table to which data is to be inserted.
Data is inserting in merchant data when error occurred in customer table insertion.
please help to insert source data in both the table when exception occurs should not exist on first error
to be iterate the entire data set
create table customer
(
id number not null
, name varchar2(20)
);
create table merchant
(
id number not null
, name varchar2(20)
);
alter table customer add constraint customer_uk unique (id);
alter table merchant add constraint merchant_uk unique (id);
create table people
(
id number not null
, name varchar2(20)
);
create table TXN_ERROR_LOG
(
ERR_DESCRIPTION,
ERR_DATE
);
insert into people values(1,'Jon');
insert into people values(2,'Cersei');
insert into people values(2,'Khal');
insert into people values(3,'Gregor');
insert into people values(3,'Sansa');
insert into people values(4,'Arya');
insert into people values(5,'Eddard');
Block
SET SERVEROUTPUT ON;
declare
cursor c_people is
select id, name
from people;
type t_people is table of c_people%rowtype
index by binary_integer;
l_people t_people;
l_err_msg varchar2(1000);
l_err_count number;
bulk_errors exception;
pragma exception_init(bulk_errors, -24381);
begin
open c_people;
fetch c_people bulk collect into l_people;
close c_people;
dbms_output.put_line ('Before FORALL');
dbms_output.put_line ('Total Count = '||l_people.count);
begin
-- bulk insert and save exceptions
forall i in l_people.FIRST..l_people.LAST save exceptions
insert into customer
(
id, name
)
values
(
l_people(i).id, l_people(i).name
);
forall i in l_people.FIRST..l_people.LAST save exceptions
insert into merchant
(
id, name
)
values
(
l_people(i).id, l_people(i).name
);
exception
when bulk_errors then
l_err_count := sql%bulk_exceptions.count;
for i in 1 .. l_err_count loop
l_err_msg := 'Error occured' || i
|| '; Array index: '
|| sql%bulk_exceptions(i).error_index || ': '
|| sqlerrm(-sql%bulk_exceptions(i).error_code);
INSERT INTO TXN_ERROR_LOG
(
ERR_DESCRIPTION,
ERR_DATE
)
VALUES(
l_err_msg,
SYSDATE
);
end loop;
end;
dbms_output.put_line ('After FORALL');
end;
/
please help experts.