Home
Blocks containing EXECUTE IMMEDIATE should trap all exceptions
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;