Oracle PL/SQL Programming Guide to Oracle 8i Features

Oracle PL/SQL Programming Guide to Oracle 8i FeaturesSearch this book
Previous: 3.4 Combining the Definer and Invoker Rights ModelsChapter 4Next: 4.2 NDS Statement Summary
 

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:

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:

Exclusive DBMS_SQL capabilities:

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


Previous: 3.4 Combining the Definer and Invoker Rights ModelsOracle PL/SQL Programming Guide to Oracle 8i FeaturesNext: 4.2 NDS Statement Summary
3.4 Combining the Definer and Invoker Rights ModelsBook Index4.2 NDS Statement Summary

The Oracle Library Navigation

Copyright (c) 2000 O'Reilly & Associates. All rights reserved.

Library Home Oracle PL/SQL Programming, 2nd. Ed. Guide to Oracle 8i Features Oracle Built-in Packages Advanced PL/SQL Programming with Packages Oracle Web Applications Oracle PL/SQL Language Pocket Reference Oracle PL/SQL Built-ins Pocket Reference