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;