Oracle Built-in Packages

Chapter 2. Executing Dynamic SQL and PL/SQL

The DBMS_SQL package offers access to dynamic SQL and dynamic PL/SQL from within PL/SQL programs. “Dynamic” means that the SQL statements you execute with this package are not prewritten into your programs. They are, instead, constructed at runtime as character strings and then passed to the SQL engine for execution.

The DBMS_SQL package allows you to perform actions that are otherwise impossible from within PL/SQL programs, including:

Execute DDL statements

DDL (Data Definition Language) statements, such as DROP TABLE or CREATE INDEX, are not legal in native PL/SQL. On the other hand, you can use DBMS_SQL to issue any DDL statement and create generic programs to perform such actions as dropping the specified table. Of course, your session will still need the appropriate database privileges to perform the requested actions.

Build an ad-hoc query interface

With DBMS_SQL, you no longer have to hard-code a SELECT statement for a query or a cursor. Instead, you can let a user specify different sort orders, conditions, and any other portion of a SELECT statement.

Execute dynamically constructed PL/SQL programs

In a database table you can store the names of procedures that perform certain calculations. Then build a front-end to that table, which allows a user to select the computation of interest, provide the inputs to that program, and then execute it. When other computations need to be offered to the user, you add a row in a table, instead ...