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