Oracle PL/SQL Programming, Third Edition

Comparing NDS and DBMS_SQL

Native dynamic SQL is covered in this book because it is a part of the native PL/SQL language. DBMS_SQL, on the other hand, is a built-in package, so we describe it in detail in the Oracle Built-in Packages book (O’Reilly). But is there any reason to use DBMS_SQL now that NDS is available?

Eyeballing Equivalent Implementations

First, 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'); ...