G-3220: Always process saved exceptions from a FORALL statement.
Major
Reliability, Testability
Reason
Using save exceptions in a forall statement without actually processing the saved exceptions is just wasted work.
If your use of forall is meant to be atomic (all or nothing), don't use save exceptions. If you want to handle errors of individual rows and do use save exceptions, always include an exception handler block with a loop to process the saved exceptions.
Example (bad)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17 | declare
t_employee_ids employee_api.t_employee_ids_type;
co_increase constant employees.salary%type := 0.1;
co_department_id constant departments.department_id%type := 10;
e_bulk_errors exception;
pragma exception_init(e_bulk_errors,-24381);
begin
t_employee_ids := employee_api.employee_ids_by_department(
id_in => co_department_id
);
<<process_employees>>
forall i in 1..t_employee_ids.count() save exceptions
update employees
set salary = salary + (salary * co_increase)
where employee_id = t_employee_ids(i);
end;
/
|
Example (good)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24 | declare
t_employee_ids employee_api.t_employee_ids_type;
co_increase constant employees.salary%type := 0.1;
co_department_id constant departments.department_id%type := 10;
e_bulk_errors exception;
pragma exception_init(e_bulk_errors,-24381);
begin
t_employee_ids := employee_api.employee_ids_by_department(
id_in => co_department_id
);
<<process_employees>>
forall i in 1..t_employee_ids.count() save exceptions
update employees
set salary = salary + (salary * co_increase)
where employee_id = t_employee_ids(i);
exception
when e_bulk_errors then
<<handle_bulk_exceptions>>
for i in 1..sql%bulk_exceptions.count
loop
logger.log(sql%bulk_exceptions(i).error_code);
end loop handle_bulk_exceptions;
end;
/
|