Hello,
I was trying to understand the usage of sql%bulk_exceptions:
I have the script:
declare
type NumList is table of number;
num_tab NumList := NumList(100, 0, 110, 300, 0, 199, 200, 0, 400);
bulk_errors exception;
pragma exception_init(bulk_errors, -24381);
begin
forall i in num_tab.first .. num_tab.last
save exceptions
delete from orders where order_total < 500000/num_tab(i);
exception when bulk_errors then
dbms_output.put_line('number of errors is:' || sql%bulk_exceptions.count);
for j in 1 .. sql%bulk_exceptions.count loop
dbms_output.put_line(to_char(sql%bulk_exceptions(j).error_index) || '/' || sqlerrm(sql%bulk_exceptions(j).error_code));
end loop;
end;
runs fine, and returns :
number of errors is:3
2/ -1476: non-ORACLE exception
5/ -1476: non-ORACLE exception
8/ -1476: non-ORACLE exception
but if i modify it, and replace EXCEPTION WHEN bulk_errors THEN with EXCEPTIONS WHEN zero_divide THEN (because this is the error that is thrown when trying to divide by 0), the code doesn't execute correctly:
declare
type NumList is table of number;
num_tab NumList := NumList(100, 0, 110, 300, 0, 199, 200, 0, 400);
begin
forall i in num_tab.first .. num_tab.last
save exceptions
delete from orders where order_total < 500000/num_tab(i);
exception when ZERO_DIVIDE THEN
dbms_output.put_line('number of errors is:' || sql%bulk_exceptions.count);
for j in 1 .. sql%bulk_exceptions.count loop
dbms_output.put_line(to_char(sql%bulk_exceptions(j).error_index) || '/' || sqlerrm(sql%bulk_exceptions(j).error_code));
end loop;
end;