Home

EXECUTE IMMEDIATE should be used instead of DBMS_SQL procedure calls

Rule description

  • EXECUTE IMMEDIATE should be used instead of 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;
Visual Expert 2020
 VEPLSQLRULE78