Advanced Oracle PL/SQL Programming with Packages

2.7. Building Windows Into Your Packages

A special kind of toggle can be used to provide what I call a window into a package. This window allows a restricted view into the inner workings of a package, which can be critical to making the package usable in a complex, multilayered application.

As I've explained in Chapter 1, packages are broken up into the specification and the body. The specification defines those elements that can be called from outside of the package (the public elements). The body contains the implementation of public elements and also of private elements (those elements that can only be referenced inside the body of the package). This dichotomy allows us to hide quite securely implementation details that users need not be aware of in order to make use of the package.

This "information hiding" aspect of packages is a great feature—until a developer needs to know what is going on inside the package. The black box in this case can become a hindrance. For example, I built a package called PLVdyn (which stands for "PL/Vision DYNamic SQL") to make it easier for developers to use the built-in DBMS_SQL package. PLVdyn lets the user parse and execute dynamically constructed SQL and PL/SQL statements without fussing with all the details inherent in the built-in package.

With PLVdyn, you construct a SQL statement and pass that string to a PLVdyn program for parsing or execution. It's a big time-saver, but it also implies a loss of some control. You trust PLVdyn ...