15.6 Nds Utility Package

To make it easier for you to take advantage of the various generic utilities discussed in this chapter, I have created a single package called ndsutil. This package, available from the O'Reilly site in ndsutil.pkg, contains the programs listed in Table 15-1.

Table 15-1. Contents of the NDS utility package on the O'Reilly site
Name	Description
execImmed	A substitute for EXECUTE IMMEDIATE that does not need a USING or INTO clause; includes error handling.
openFor	A substitute for OPEN FOR that does not need a USING clause; includes error handling.
showCol	Shows the contents of a single column in the specified table.
tabCount	Returns the number of rows in the specified table, with an optional WHERE clause.
countBy	Returns the number of rows in the specified table for a particular GROUP BY expression, with an optional HAVING clause.
dynPLSQL	Executes a dynamic PL/SQL string, automatically making sure that it is a valid block and that it ends in a semicolon. The USING clause is not allowed.

The package is defined using the invoker rights mode (AUTHID CURRENT_USER). This means that no matter who owns the package, any external references in the dynamic SQL you execute via ndsutil are resolved according to the authority of the invoking schema, not the owner.

All programs contain exception sections that display the error and the offending SQL. Procedures then re-raise the error, whereas functions generally return NULL or a NULL/empty structure.

15.7 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?

15.7.1 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');

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'll write dramatically less code using NDS. And because the code you write will rely less on built-in packaged programs and more on native, standard elements of PL/SQL, that code is easier to build, read, and maintain.