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