Home

COMMIT should not be used inside a loop

Description

    The rule "COMMIT should not be used inside a loop" states that the COMMIT statement should not be used inside a loop in PL/SQL code. This is because a COMMIT statement will cause the entire transaction to be committed, and any subsequent changes made within the loop will not be saved. Instead, the COMMIT statement should be used after the loop has been completed, so that all changes are committed at once.

Key Benefits

  • Eliminates unnecessary commits: Committing inside a loop can lead to multiple commits being made when only one is necessary.
  • Improves performance: Committing inside a loop can significantly slow down the execution of the loop.
  • Maintains data integrity: Committing inside a loop can lead to data being committed before it is valid, which can cause data integrity issues.

 

Non-compliant Code Example

BEGIN
  FOR i IN 1 .. CUSTOMER_TABLE.COUNT
  LOOP
		IF CUSTOMER_TABLE(i) IS NOT NULL THEN
			DBMS_OUTPUT.PUT( i || ' = (' || CUSTOMER_TABLE(i).Name || ', ' || CUSTOMER_TABLE(i).PhoneNumber || ')' );
		ELSE
			DBMS_OUTPUT.PUT( i || ' IS NULL' );
		END IF;
		
		COMMIT; --Non compliant code (COMMIT is used inside loop)
  END LOOP;
END;

Compliant Code Example

BEGIN
  FOR i IN 1 .. CUSTOMER_TABLE.COUNT
  LOOP
		IF CUSTOMER_TABLE(i) IS NOT NULL THEN
			DBMS_OUTPUT.PUT( i || ' = (' || CUSTOMER_TABLE(i).Name || ', ' || CUSTOMER_TABLE(i).PhoneNumber || ')' );
		ELSE
			DBMS_OUTPUT.PUT( i || ' IS NULL' );
		END IF;
		
  END LOOP;

    COMMIT;     --Compliant code (COMMIT is used outside loop)

END;
Visual Expert 2024
 VEPLSQLRULE25