Oracle Built−in Packages- P11: Ah, for the good old days of Version of PL /SQL! Life was so simple then. No stored procedures or functions and certainly no packages. You had your set of built−in functions, like SUBSTR and TO_DATE. You had the IF statement and various kinds of loops. With these tools at hand, you built your batch−processing scripts for execution in SQL*Plus, and you coded your triggers in SQL*Forms , and you went home at night content with a good day's work done. | Appendix A What s on the Companion Disk Copyright c 2000 O Reilly Associates. All rights reserved. The DBMS_STANDARD Package 41 Oracle Built-in Packages SEARCH PREVIOUS Chapter 2 NEXT 42 2. Executing Dynamic SQL and PL SQL Contents Examples of Dynamic SQL Getting Started with DBMS_SQL The DBMS_SQL Interface Tips on Using Dynamic SQL DBMS_SQL Examples 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 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
