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.
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:
Allows you to fetch multiple columns of information directly into a PL/SQL record. With DBMS_SQL, you must fetch into individual variables.
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).
Supports SQL statements that are more than 32KB in length.
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).
Copyright (c) 2000 O'Reilly & Associates. All rights reserved.