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;