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 ). |
Team-Fly |
Top |