1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41
| create or replace procedure import_in_call is
cursor C is
select * from pmt.m_in_call;
type array is table of c%rowtype;
l_data array;
dml_errors EXCEPTION;
PRAGMA exception_init(dml_errors, -24381);
l_errors number;
l_errno number;
l_msg varchar2(4000);
l_idx number;
begin
open c;
loop
fetch c bulk collect into l_data limit 100;
begin
forall i in 1 .. l_data.count SAVE EXCEPTIONS
insert into stg_in_call values l_data(i);
exception
when others then
l_errors := sql%bulk_exceptions.count;
for i in 1 .. l_errors
loop
l_errno := sql%bulk_exceptions(i).error_code;
l_msg := sqlerrm(-l_errno);
l_idx := sql%bulk_exceptions(i).error_index;
insert into errors_diary(error_num, error_sysdate, error_desc, error_val)
values(
l_errno,
l_data(l_idx).TIMESTAMP,
l_msg,
l_idx);
end loop;
end;
exit when c%notfound;
end loop;
close c;
end;
/
execute import_in_call |
Partager