Rule description

• Blocks containing EXECUTE IMMEDIATE should trap all exceptions

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;