Home

Description

The rule "Blocks containing EXECUTE IMMEDIATE should trap all exceptions" states that when using the EXECUTE IMMEDIATE command in a PL/SQL block, all exceptions should be trapped. This means that any errors that occur during the execution of the command should be handled by the PL/SQL block. This is done by using the EXCEPTION clause, which allows the programmer to define how the errors should be handled. This ensures that the program can continue to execute even if an error occurs. This is important for ensuring that the program does not crash or produce unexpected results.

Key Benefits

• Trap all exceptions: Blocks containing EXECUTE IMMEDIATE should trap all exceptions, ensuring that any errors are handled gracefully.
• Improved performance: EXECUTE IMMEDIATE can improve the performance of your code by avoiding the need to compile the SQL statement each time it is used.
• Flexibility: EXECUTE IMMEDIATE allows you to dynamically create and execute SQL statements, giving you greater flexibility in your code.

Non-compliant Code Example

BEGIN
  SELECT COLUMN_NAME INTO v_column FROM USER_TAB_COLS 
    WHERE TABLE_NAME = 'CUSTOMER' AND COLUMN_NAME = Cust_column;
  sql_stmt := 'UPDATE CUSTOMER SET AreaCode = :1 WHERE ' 
               || v_column || ' = :2';
  EXECUTE IMMEDIATE sql_stmt USING areaCode, column_value;  --Non compliant code (Blocks containing EXECUTE IMMEDIATE is not trapping all the exceptions)
  IF SQL%ROWCOUNT > 0 THEN
    DBMS_OUTPUT.PUT_LINE('Area code have been updated for: ' || Cust_column 
                        || ' = ' || column_value);
  END IF;
END update_cust_areacode;

Compliant Code Example

BEGIN
  SELECT COLUMN_NAME INTO v_column FROM USER_TAB_COLS 
    WHERE TABLE_NAME = 'CUSTOMER' AND COLUMN_NAME = Cust_column;
  sql_stmt := 'UPDATE CUSTOMER SET AreaCode = :1 WHERE ' 
               || v_column || ' = :2';
  EXECUTE IMMEDIATE sql_stmt USING areaCode, column_value;  --Compliant code (Blocks containing EXECUTE IMMEDIATE is trapping all the exceptions)
  IF SQL%ROWCOUNT > 0 THEN
    DBMS_OUTPUT.PUT_LINE('Area code have been updated for: ' || Cust_column 
                        || ' = ' || column_value);
  END IF;
  EXCEPTION
  WHEN NO_DATA_FOUND THEN
    DBMS_OUTPUT.PUT_LINE ('Invalid Column: ' || Cust_column);
END update_cust_areacode;