G-3310

🆓

Error

Never commit within a cursor loop.

Rule Details

Severity Level: Blocker
Quality Area: Reliability
Fixing times: 5 mins
Local-medium
Database Support: OracleDB (v7.3.4 +)

Reason

Doing frequent commits within a cursor loop (all types of loops over cursors, whether implicit cursor for loop or loop with explicit fetch from cursor or cursor variable) risks not being able to complete due to ORA-01555, gives bad performance, and risks that the work is left in an unknown half-finished state and cannot be restarted.

If the work belongs together (an atomic transaction) the commit should be moved to after the loop. Or even better if the logic can be rewritten to a single DML statement on all relevant rows instead of a loop, committing after the single statement.
If each loop iteration is a self-contained atomic transaction, consider instead to populate a collection of transactions to be done (taking restartability into account by collection population), loop over that collection (instead of looping over a cursor) and call a procedure (that contains the transaction logic and the commit) in the loop (see also G-3320.

Examples

Non-Compliant Example

declare
   l_counter          integer                           := 0;
   l_discount         discount.percentage%type;
   co_status_new      constant orders.order_status%type := 'New';
   co_commit_interval constant integer                  := 100;
begin
   <<new_orders>>
   for r_order in (
      select o.order_id,o.customer_id
        from orders o
       where o.order_status = co_status_new
   )
   loop
      l_discount := sales_api.calculate_discount(p_customer_id => r_order.customer_id);

      update order_lines ol
         set ol.discount = l_discount
       where ol.order_id = r_order.order_id;

      l_counter  := l_counter + 1;
      if l_counter = co_commit_interval then
         commit;
         l_counter := 0;
      end if;
   end loop new_orders;
   if l_counter > 0 then
      commit;
   end if;
end;
/

Issues

Line	Column	Message
22	10	Committing in cursor loop.

Compliant Solution - ★★★★☆

declare
   l_discount    discount.percentage%type;
   co_status_new constant orders.order_status%type := 'New';
begin
   <<new_orders>>
   for r_order in (
      select o.order_id,o.customer_id
        from orders o
       where o.order_status = co_status_new
   )
   loop
      l_discount := sales_api.calculate_discount(p_customer_id => r_order.customer_id);

      update order_lines ol
         set ol.discount = l_discount
       where ol.order_id = r_order.order_id;
   end loop new_orders;

   commit;
end;
/

Compliant Solution - ★★★★★

declare
   co_status_new constant orders.order_status%type := 'New';
begin
   update (
             select o.customer_id,ol.discount
               from orders o
               join order_lines ol
                 on ol.order_id = o.order_id
              where o.order_status = co_status_new
          )
      set discount = sales_api.calculate_discount(p_customer_id => customer_id);

   commit;
end;
/

Explanation

(Assuming suitable foreign key relationship exists to allow updating a join.)

References

same as plsql:S3830
same as Trivadis G-3310