G-3220

🆓

Warning

Always process saved exceptions from a FORALL statement.

Rule Details

Severity Level: Critical
Quality Area: Reliability
Fixing times: 10 mins
Quick Fix | Local-high
Database Support: OracleDB (v8.1.5 +)

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.

Examples

Non-Compliant Example

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;
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;
/

Issues

Line	Column	Message
10	42	Ignoring exceptions saved in forall statement.

Compliant Solution - ★★★☆☆

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;
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()
      update employees
         set salary = salary + (salary * co_increase)
       where employee_id = t_employee_ids(i);
end;
/

Removing the 'save exceptions' clause simplifies the code and frees up unused resources. However, if an exception occurs, it is difficult to determine the root cause.

Compliant Solution - ★★★★★

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;
/

This solution logs all exceptions individually, which makes it easier to identify the root cause.

References

same as Trivadis G-3220
similar to plsql:ForallStatementShouldUseSaveExceptionsClause
The scope of plsql:ForallStatementShouldUseSaveExceptionsClause is every FORALL statement, it enforces a SAVE EXCEPTIONS clause.