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'); ...
Get Oracle PL/SQL Programming, Third Edition now with the O’Reilly learning platform.
O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.