G-3310: Never commit within a cursor loop.

Critical

Efficiency, Reliability

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).

Example (bad)

declare
   l_counter  integer := 0;
   l_discount discount.percentage%type;
begin
   for r_order in (
      select o.order_id,o.customer_id
        from orders o
       where o.order_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 = 100 then
         commit;
         l_counter := 0;
      end if;
   end loop;
   if l_counter > 0 then
      commit;
   end if;
end;
/

Example (good)

declare
   l_discount discount.percentage%type;
begin
   for r_order in (
      select o.order_id,o.customer_id
        from orders o
       where o.order_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;

   commit;
end;
/

Example (best)

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

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 = 'New'
          )
      set discount = sales_api.calculate_discount(p_customer_id => customer_id);

   commit;
end;
/