Create Pl/SQL block dynamically and then execute it by calling 'DBMS_SQL.EXECUTE' : DBMS_SQL « System Packages « Oracle PL/SQL Tutorial

1. Oracle PL/SQL Tutorial
2. System Packages
3. DBMS_SQL

SQL>
SQL> CREATE OR REPLACE PROCEDURE assign (
  2     val_in IN VARCHAR2,
  3     varname_in IN VARCHAR2,
  4     trc IN BOOLEAN := TRUE)
  5  IS
  6     cur PLS_INTEGER := DBMS_SQL.OPEN_CURSOR;
  7     returnValue PLS_INTEGER;
  8     assign_string VARCHAR2(2000) := 'BEGIN ' || varname_in || ' := ''' || val_in || '''; END;';
  9  BEGIN
 10     IF trc THEN DBMS_OUTPUT.PUT_LINE (assign_string); END IF;
 11     DBMS_SQL.PARSE (cur, assign_string, DBMS_SQL.NATIVE);
 12     returnValue := DBMS_SQL.EXECUTE (cur);
 13     DBMS_SQL.CLOSE_CURSOR (cur);
 14  END;
 15  /

Procedure created.

SQL>
SQL>