Although it is possible to execute DML statements with static strings in PL/SQL, the dynamic nature of DBMS_SQL does offer many new opportunities. The PLVdyn packages offers a programmatic interface for several common DML operations to make it easier for you to take advantage of this technology.
PLVdyn offers three different DML operations: dml_insert_select, dml_delete, and dml_update. There are many other possibilities for dynamic DML in PLVdyn; I encourage you to experiment with your own extensions to this package.
You can execute an INSERT-SELECT FROM statement using the dml_insert_select. The header for this procedure is:
PROCEDURE dml_insert_select (table_in IN VARCHAR2, select_in IN VARCHAR2);
In the first argument you provide the name of the table that is going to receive the rows from the SELECT statement. The second argument is the SELECT statement itself. Here is an example of ...