Oracle PL/SQL Programming: A Developer's Workbook

Chapter 13. Native Dynamic SQL

Ever since Oracle 7.1, PL/SQL developers have been able to use the built-in DBMS_SQL package to execute dynamic SQL and PL/SQL. This means that at runtime, you can construct the query, the DELETE or CREATE TABLE statement, or even the PL/SQL block as a string and then execute it. Dynamic SQL comes in extremely handy when you are building ad-hoc query systems, when you need to execute DDL inside PL/SQL, and just generally when you don’t know in advance exactly what you need to do or what the user will want to do. Dynamic SQL is a frequent requirement in web-based applications.

The problem with DBMS_SQL is that it’s a complicated package; it has a number of restrictions (such as not recognizing and working with new Oracle8 datatypes); and it’s relatively slow. So our dear friends at PL/SQL Central in Redwood Shores took pity on us all and reimplemented dynamic SQL directly in the PL/SQL 8.1 language itself. This feature, called native dynamic SQL (NDS), is available only in Oracle8i.

Beginner

13-1.	What are the two statements added to the PL/SQL language to implement native dynamic SQL?
13-2.	Write an anonymous block that drops the employee table.
13-3.	Write a procedure that drops whichever table you pass to the procedure.
13-4.	Write a function that returns the number of rows in the specified table.