Oracle PL/SQL Programming Guide to Oracle 8i Features

Oracle PL/SQL Programming Guide to Oracle 8i FeaturesSearch this book
Previous: 4.6 Building Applications with NDSChapter 4
Native Dynamic SQL in Oracle8i
Next: 5. Bulking Up with PL/SQL 8.1
 

4.7 NDS Utility Package

To make it easier for my readers to take advantage of the various generic utilities discussed in this chapter, I have created a single package called ndsutil. This package, available on the companion disk in ndsutil.pkg, contains the programs listed in Table 4.1.


Table 4.1: Contents of the NDS Utility Package

Name

Description

execImmed

Substitute for EXECUTE IMMEDIATE that does not need a USING or INTO clause; includes error handling.

openFor

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 reraise the error, whereas functions generally return NULL or a NULL/empty structure.

TIP: I am sure there are many programs you can add to this package. Please post any additions to the PL/SQL Pipeline at http://www.revealnet.com/plsql-pipeline, in the Pipetalk area. Then we can all gain from your experience and creativity!


Previous: 4.6 Building Applications with NDSOracle PL/SQL Programming Guide to Oracle 8i FeaturesNext: 5. Bulking Up with PL/SQL 8.1
4.6 Building Applications with NDSBook Index5. Bulking Up with PL/SQL 8.1

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