Home

Description

The EXECUTE IMMEDIATE should be used instead of DBMS_SQL procedure calls rule states that when executing dynamic SQL statements, the EXECUTE IMMEDIATE statement should be used instead of the DBMS_SQL package. EXECUTE IMMEDIATE is a PL/SQL statement that can be used to execute dynamic SQL statements. It is more efficient than the DBMS_SQL package, which requires more code and is less secure. This rule helps to ensure that dynamic SQL statements are executed in a secure and efficient manner.

Key Benefits

• Speed: EXECUTE IMMEDIATE is faster than DBMS_SQL procedure calls.
• Simplicity: EXECUTE IMMEDIATE is simpler to use than DBMS_SQL procedure calls.
• Flexibility: EXECUTE IMMEDIATE allows for more flexibility than DBMS_SQL procedure calls.
• Security: EXECUTE IMMEDIATE is more secure than DBMS_SQL procedure calls.

Non-compliant Code Example

CREATE OR REPLACE PROCEDURE DeleteCustomer() 
IS 
   cursor_name INTEGER;
BEGIN
   cursor_name := dbms_sql.open_cursor;
   DBMS_SQL.PARSE(cursor_name, 'DELETE FROM CUSTOMERS WHERE isNotActive',DBMS_SQL.NATIVE);   --Non compliant code (DBMS_SQL is used in the procedure calls)
   DBMS_SQL.CLOSE_CURSOR(cursor_name);
END;

Compliant Code Example

CREATE OR REPLACE PROCEDURE DeleteCustomer() 
IS 
   cursor_name INTEGER;
BEGIN
    EXECUTE IMMEDIATE 'DELETE FROM CUSTOMERS WHERE isNotActive';    --Compliant code (EXECUTE IMMEDIATE is used in the procedure calls)
END;