EXECUTE IMMEDIATE is easier to use and understand than the DBMS_SQL package’s procedures. It should therefore be preferred, when
possible.
Noncompliant code example
SET SERVEROUTPUT ON
CREATE TABLE myTable(
foo VARCHAR2(42)
);
CREATE PROCEDURE drop_table(tableName VARCHAR2) AS
cursorIdentifier INTEGER;
BEGIN
cursorIdentifier := DBMS_SQL.OPEN_CURSOR; -- Compliant; this is not a procedure call
DBMS_SQL.PARSE(cursorIdentifier, 'DROP TABLE ' || tableName, DBMS_SQL.NATIVE); -- Noncompliant
DBMS_SQL.CLOSE_CURSOR(cursorIdentifier); -- Noncompliant
DBMS_OUTPUT.PUT_LINE('Table ' || tableName || ' dropped.');
EXCEPTION
WHEN OTHERS THEN
DBMS_SQL.CLOSE_CURSOR(cursorIdentifier); -- Noncompliant
END;
/
BEGIN
drop_table('myTable');
END;
/
DROP PROCEDURE drop_table;
Compliant solution
SET SERVEROUTPUT ON
CREATE TABLE myTable(
foo VARCHAR2(42)
);
CREATE PROCEDURE drop_table(tableName VARCHAR2) AS
cursorIdentifier INTEGER;
BEGIN
EXECUTE IMMEDIATE 'DROP TABLE ' || tableName;
DBMS_OUTPUT.PUT_LINE('Table ' || tableName || ' dropped.');
END;
/
BEGIN
drop_table('myTable');
END;
/
DROP PROCEDURE drop_table;
