Home

FORALL statements should use the SAVE EXCEPTIONS clause

Description

    The FORALL statement in PL/SQL is used to execute a set of DML statements in a single call, and can improve performance when used correctly. The SAVE EXCEPTIONS clause should be used when using the FORALL statement to ensure that any exceptions encountered during the execution of the DML statements are saved and can be inspected later. This clause allows the user to identify which statements failed and why, and can be used to take corrective action if necessary.

Key Benefits

  • SAVE EXCEPTIONS clause - Prevents exceptions from being raised in the FORALL statement, allowing the rest of the statement to execute.
  • Increased Performance - Allows for more efficient processing as exceptions are not raised, improving performance.
  • Improved Error Handling - Exceptions are handled more effectively, allowing for better control over the code.

 

Non-compliant Code Example

BEGIN
  FORALL j IN CUSTOMER_TABLE.FIRST..CUSTOMER_TABLE.LAST    --Non compliant code (FORALL statements does not used SAVE EXCEPTIONS clause)
    UPDATE CUSTOMERS SET minimunExpense = 1800
    WHERE 100 < CUSTOMER_TABLE(j);
 
EXCEPTION
  WHEN OTHERS THEN
    error_message := SQLERRORMSG;
    DBMS_OUTPUT.PUT_LINE (error_message);
 
    COMMIT;  
    RAISE;
END;

Compliant Code Example

BEGIN
  FORALL j IN CUSTOMER_TABLE.FIRST..CUSTOMER_TABLE.LAST SAVE EXCEPTIONS  --Compliant code (FORALL statements used SAVE EXCEPTIONS clause)
    UPDATE CUSTOMERS SET minimunExpense = 1800
    WHERE 100 < CUSTOMER_TABLE(j);
 
EXCEPTION
  WHEN OTHERS THEN
    error_message := SQLERRORMSG;
    DBMS_OUTPUT.PUT_LINE (error_message);
 
    COMMIT;  
    RAISE;
END;
Visual Expert 2024
 VEPLSQLRULE18