Dynamic DQL with DBMS_SQL package : Dynamic SQL statement « PL SQL Statements « Oracle PL/SQL Tutorial

1. Oracle PL/SQL Tutorial
2. PL SQL Statements
3. Dynamic SQL statement

SQL>
SQL> CREATE OR REPLACE PROCEDURE runddl (ddl_in in VARCHAR2)
  2  IS
  3     cur INTEGER:= DBMS_SQL.OPEN_CURSOR;
  4     returnValue INTEGER;
  5  BEGIN
  6     DBMS_SQL.PARSE (cur, ddl_in, DBMS_SQL.NATIVE);
  7     returnValue := DBMS_SQL.EXECUTE (cur);
  8     DBMS_SQL.CLOSE_CURSOR (cur);
  9  EXCEPTION
 10     WHEN OTHERS
 11     THEN
 12        DBMS_OUTPUT.PUT_LINE (
 13           'RunDDL Failure on ' || ddl_in);
 14        DBMS_OUTPUT.PUT_LINE (SQLERRM);
 15        DBMS_SQL.CLOSE_CURSOR (cur);
 16  END;
 17  /

Procedure created.

SQL>
SQL>
SQL>