Home

COMMIT should not be used inside a loop

Rule description

  • COMMIT should not be used inside a loop

 

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 2020
 VEPLSQLRULE25