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. infoclober
The DBMS_SQL package allows you to perform actions that are otherwise impossible from within PL/SQL programs, including:
Execute DDL statements infoclober
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 infoclober
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 of modifying one or more screens.
DBMS_SQL is simultaneously one of the most complex, most useful, and most rewarding of the built-in packages. It may take some time for you to get comfortable with how to apply the technology. Once you are up and running, however, you will be amazed at the feats you will be able to perform!