G-3220

🆓

Warning

Always process saved exceptions from a FORALL statement.

Rule Details

Severity Level: Critical
Quality Area: Reliability
Fixing times: 10 mins
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.

Example

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

Issues

Line	Column	Message
12	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;
   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;
/

References

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