Advanced Oracle PL/SQL Programming with Packages

19.5. DML Operations

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 cannot include bind variables for any of the DML programs in PLVdyn. Generally, PLVdyn does not support bind variables. The consequence of this is that the WHERE clauses may not contain any colons unless they are embedded in a literal string. The PLVdyn1 package (described on the companion disk; see http://examples.oreilly.com/advoracle) does allow you to specify single bind variable values for a variety of DML statements.

19.5.1. Performing an INSERT-SELECT FROM

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 ...