Advanced Oracle PL/SQL Programming with Packages

19.4. PLVdyn: A Code Layer over DBMS_SQL

The PLVdyn (PL/Vision DYNamic SQL) package offers an easy-to-use, programmatic interface, or API, to the DBMS_SQL builtin package. It combines many basic operations in DBMS_SQL into functions and procedures, freeing up developers from having to know about many of the intricacies of using the builtin package.

Operations include:

• Execute a Data Definition Language (DDL) statement with one line of code

• Execute a PL/SQL block with one line of code

• Delete from or truncate the specified table

• Drop one or more objects from the data dictionary

• Execute an INSERT...SELECT FROM statement with an absolute minimum of SQL coding

• View the contents of the specified table

• Toggle on/off tracing that displays the dynamic SQL statement being parsed

The following sections show how to use each of the different elements of the PLVdyn package.

19.4.1. DDL Operations

The PLVdyn package offers several programmatic interfaces to DDL or Data Definition Language commands. Before DBMS_SQL was available, you could not execute any DDL statements (such as CREATE TABLE, CREATE INDEX, etc.) within PL/SQL. Now DBMS_SQL lets you execute anything you want—as long as you have the appropriate authority.

DDL statements in SQL are handled differently from DML (Data Manipulation Language) statements such as UPDATE, INSERT, and so on. There is no need to execute a DDL statement. The simple act of parsing DDL automatically executes it and performs a COMMIT. This is true in SQL*Plus ...