4. Native Dynamic SQL in Oracle8i

Contents:
DBMS_SQL Versus NDS
NDS Statement Summary
Multirow Queries with Cursor Variables
Binding Variables
Working with Objects and Collections
Building Applications with NDS
NDS Utility Package

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 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.

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 use -- when 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.

4.1 DBMS_SQL Versus NDS

Let's compare the DBMS_SQL and NDS implementations of a program that displays all the employees for the specified and very dynamic WHERE clause.

The DBMS_SQL implementation:

CREATE OR REPLACE PROCEDURE showemps (
   where_in IN VARCHAR2 := NULL)
IS
   cur INTEGER := DBMS_SQL.OPEN_CURSOR;
   rec employee%ROWTYPE;
   fdbk INTEGER;
BEGIN
   DBMS_SQL.PARSE
     (cur, 
      'SELECT employee_id, last_name 
         FROM employee 
        WHERE ' || NVL (where_in, '1=1'),
      DBMS_SQL.NATIVE);

   DBMS_SQL.DEFINE_COLUMN (cur, 1, 1);
   DBMS_SQL.DEFINE_COLUMN (cur, 2, user, 30);

   fdbk := DBMS_SQL.EXECUTE (cur);
   LOOP
      /* Fetch next row. Exit when done. */
      EXIT WHEN DBMS_SQL.FETCH_ROWS (cur) = 0;
      DBMS_SQL.COLUMN_VALUE (cur, 1, rec.employee_id);
      DBMS_SQL.COLUMN_VALUE (cur, 2, rec.last_name);
      DBMS_OUTPUT.PUT_LINE (
         TO_CHAR (rec.employee_id) || '=' || 
         rec.last_name);
   END LOOP;

   DBMS_SQL.CLOSE_CURSOR (cur);
END;
/

The NDS implementation:

CREATE OR REPLACE PROCEDURE showemps (
   where_in IN VARCHAR2 := NULL)
IS
   TYPE cv_typ IS REF CURSOR;
   cv cv_typ;
   v_id employee.employee_id%TYPE;
   v_nm employee.last_name%TYPE;
BEGIN
   OPEN cv FOR 
      'SELECT employee_id, last_name 
         FROM employee 
        WHERE ' || NVL (where_in, '1=1');
   LOOP
      FETCH cv INTO v_id, v_nm;
      EXIT WHEN cv%NOTFOUND;
      DBMS_OUTPUT.PUT_LINE (
         TO_CHAR (v_id) || '=' || v_nm);
   END LOOP;
   CLOSE cv;
END;
/

As you can see (and this is true in general), you can write dramatically less code using NDS. And since the code you write relies less on built-in packaged programs and more on native, standard elements of PL/SQL, that code is easier to build, read, and maintain.

Given this situation, why would anyone use DBMS_SQL ever again? Because NDS cannot do everything and anything you might want to do. The following lists show the operations that can be performed exclusively by each of these dynamic SQL implementations.

Exclusive NDS capabilities:

Works with all SQL datatypes, including user-defined objects and collection types (variable arrays, nested tables, and index-by tables). DBMS_SQL only works with Oracle7-compatible datatypes.
Allows you to fetch multiple columns of information directly into a PL/SQL record. With DBMS_SQL, you must fetch into individual variables.

Exclusive DBMS_SQL capabilities:

Supports Method 4 dynamic SQL, which means that at compile time, you don't know how many columns you will be querying and/or how many bind variables will need to be set. Method 4 is the most complex form of dynamic SQL, and NDS doesn't support it (except under certain restricted circumstances).
As of Oracle8, allows you to describe the columns of your dynamic cursor, obtaining column information in an index-by table of records.
Supports SQL statements that are more than 32KB in length.
Supports the use of the RETURNING clause into an array of values; NDS only allows the use of RETURNING for a single statement.
Allows you to reuse your dynamic SQL cursors, which can improve performance.
Can be executed from client-side (Oracle Developer) applications.

For more information about DBMS_SQL and the listed capabilities of this code, please see Chapter 3 of Oracle Built-in Packages (O'Reilly & Associates, 1998).

What can we conclude from these lists? The NDS implementation will be able to handle something like 80 to 90% of the dynamic SQL requirements you are likely to face. It is good to know, however, that there is still a place for DBMS_SQL (especially since I wrote a 100-page chapter on that package in Oracle Built-in Packages ).