Hello experts,
I am on 12c.
create table tab
(
id number,
val varchar2(10)
);
create table tab_log
(
r_num number,
e_code number,
e_msg varchar2(200)
);
declare
type tab_rec is record
(
id number,
val varchar2(20)
);
type tab_aat is table of tab_rec index by pls_integer;
tab_coll tab_aat;
l_error_index number;
l_error_code number;
l_error_msg varchar2(200);
e_errors exception;
pragma exception_init(e_errors, -24381);
begin
dbms_output.put_line('Starting..');
for i in 1..1000 loop
tab_coll(i).id := i;
tab_coll(i).val := 'Row '||i;
end loop;
tab_coll(100).val := rpad('X',20,'X');
tab_coll(200).val := rpad('X',20,'X');
tab_coll(300).val := rpad('X',20,'X');
forall i in indices of tab_coll save exceptions
insert into tab values tab_coll(i);
dbms_output.put_line('Done.'||tab_coll.count);
exception
when e_errors then
for i in 1..sql%bulk_exceptions.count loop
l_error_index := sql%bulk_exceptions(i).error_index;
l_error_code := sql%bulk_exceptions(i).error_code;
l_error_msg := sqlerrm(-(sql%bulk_exceptions(i).error_code));
insert into tab_log values (l_error_index, l_error_code, l_error_msg);
end loop;
end;
/
In the above code, I would like to know what data caused what error. In the table tab_log, I can store only the iteration but how do I know the exact data that caused this error. And how do I modify the code to insert such data I can fill the below table instead of the above.
Eg:
create table tab_log
(
id number,
val varchar2(10),
e_code number,
e_msg varchar2(200)
);
Regards,
Ranagal