Skip to content

G-3310: Never commit within a cursor loop.

Blocker

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)

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
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 -- NOSONAR: violates G-3210
         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;
/

Example (good)

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
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 -- NOSONAR: violates G-3210
         set ol.discount = l_discount
       where ol.order_id = r_order.order_id;
   end loop new_orders;

   commit;
end;
/

Example (best)

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

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
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;
/