Chapter 4. Native Dynamic SQL in Oracle8i

Team-Fly

---

  

	Oracle PL/SQL Programming Guide to Oracle 8 i Features By Steven Feuerstein
	Table of Contents

---

Ever since Oracle 7.1, we PL/SQL developers have been able to use the built-in DBMS_SQL package to execute dynamic SQL and PL/SQL. This means, for example, that at runtime you can construct a query, a DELETE statement, a CREATE TABLE statement, or even a PL/SQL block as a stringand 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.

But there are some problems with DBMS_SQL:

• It is a very complicated package.

• It has a number of restrictions (such as not recognizing and being able to work with new Oracle8 datatypes).

• It is 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 language itself. This new facility is called native dynamic SQL . I will refer to it as NDS in this chapter.

Here's the free advertisement for Oracle Corporation: NDS is faster and easier than DBMS_SQL. Truth in advertising? Absolutely, although my tests indicate that with the performance enhancements already in place for DBMS_SQL, NDS is on average just slightly faster. There is no doubt, however, that NDS is much easier to usewhen you can use it.

Before diving into the syntax and details of NDS, let's take a look at a comparison between the two approaches to dynamic SQL.

---

Team-Fly

Top