Advanced Oracle PL/SQL Programming with Packages

Advanced Oracle PL/SQL Programming with PackagesSearch this book
Previous: 2.6 Building Flexibility Into Your PackagesChapter 2
Best Practices for Packages
Next: 2.8 Overloading for Smart 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, PL/SQL Packages, 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 to do the right thing -- and it does. The question that is more likely in need of an answer: what is your code passing to PLVdyn?

The code we write to construct the dynamic SQL statement is often complicated. The PL/Vision packages themselves make extensive use of PLVdyn. As I tested PLVdyn, I often found that I wanted to see the SQL statement that PLVdyn was executing, so I could verify that my calling program (in PLVio or PLVlog or...) had put the SQL together properly. This was not, conceptually, a difficult problem. I could simply place calls to DBMS_OUTPUT before each of my calls to PLVdyn modules. In this way, I would not have to change PLVdyn (it is not, after all, the fault of PLVdyn -- or its author! -- that I wasn't sure what my code was doing). With this approach, if I used PLVdyn.ddl to execute a DDL statement, I could simply preface it with a call to p.l (the PL/Vision version of DBMS_OUTPUT) as follows:

p.l (ddl_statement);
PLVdyn.ddl (ddl_statement);

For all its simplicity, there is a key drawback to this solution: I would have to add calls to p.l in all the places I call a PLVdyn program. This meant going back to existing programs to make changes. I would have to remember to add this call whenever I used PLVdyn or felt the need to trace my activity. In either case, it involved changes to my code. Such changes invite misspellings and logical bugs.

This weakness, combined with the need to see what PLVdyn is doing almost caused me to abandon PLVdyn. Rather than use the package, developers would cannibalize it for the parts that seem useful. Or they would simply ignore this package-based solution and write all of their dynamic SQL directly into their programs. The result? Applications that do not reuse prebuilt code, but instead create maintenance and enhancement nightmares.

2.7.1 Centralizing the View Mechanism

A far superior approach would allow users to view the string they passed to PLVdyn without changing any of their own code. This view mechanism would be sophisticated enough to handle any number of different scenarios for SQL statement output, such as very long strings. The way to implement this approach successfully is to build the viewing feature directly into the PLVdyn package itself.

With the trace implemented inside PLVdyn, I can avoid modifying my own code when the output from that trace is needed. Instead, I can simply call a program in the PLVdyn package to tell it turn on the trace. I can then view the output until it is no longer needed and call a program to direct the package to turn off the trace. This sequence of commands is illustrated below, along with the toggle, a call to the PLVcmt which turns off commit processing. I want to run a test of my program to shift employees; I want to check my dynamic SQL without actually committing any possible mistakes.

SQL> exec PLVdyn.showsql
SQL> exec PLVcmt.turn_off
SQL> @test_move_emps
PLVdyn: INSERT INTO emp VALUES (1506, 1105, 'SMITH')
PLVdyn: UPDATE emp SET sal = 150000
PLVdyn: UPDATE emp SET hiredate = SYSDATE

I execute these steps, look over the trace, and decide that this all looks good. I then turn on commit processing, turn off the SQL trace, and run the program. All without making a single change to the move_emps program.

SQL> exec PLVdyn.noshowsql
SQL> exec PLVcmt.turn_on
SQL> @move_emps

By incorporating the trace into PLVdyn, I can't deny that I make my own job that much more difficult. I have to write the code for the trace and then figure out how best to implement it comprehensively for all PLVdyn modules. Yet once I have provided this feature, it is available for all users of PLVdyn. This kind of tradeoff (author effort vs. user ease of use) is always worthwhile in my view.

There are two aspects to keep in mind when building a trace or window into a package:

  1. You need to provide the programmatic interface so that a developer can turn on/off the trace. This interface is a typical PL/Vision toggle and will usually take the same form in any package. As a result, it is a prime candidate for generation with the PLVgen package (see the toggle and gas procedures in Chapter 15).

  2. You need to implement the trace carefully inside your package. What information will you provide? What mechanism will you use to display the trace? DBMS_OUTPUT or the p package or maybe even the PLVlog package? And, most importantly, where will you put the trace in the package so that you can minimize the number of different places it will appear? Remember: you want to avoid code redundancy. If you were aggressive about modularizing your package body, you should be able to identify a few programs or maybe even just one program (when you wish upon a star...) in which the trace can be implemented, but will then be used by all programs in the package. This process is explored in the next section.

2.7.2 Designing the Window Interface

The first step in installing a window in a package is to design the interface for the window, also referenced in this section as a trace. To do this, I must ask and answer these questions:

The easiest way for developers to specify their desires is to call a procedure. The first inclination might be to build a single procedure that accepts actions such as ON or TRACE vs. OFF or NO_TRACE as a single parameter. The header for such a procedure would look like this:

PROCEDURE set_trace (onoff_in IN VARCHAR2);

The developer would then call set_trace in SQL*Plus or another execution environment as follows:

SQL> exec PLVdyn.set_trace ('ON');
SQL> exec PLVdyn.set_trace ('OFF');

The problem with this approach is that the developer must then know what value to pass to the procedure to achieve the proper effect. Is it ON or YES? Is the value case-insensitive? Why, I ask myself in this situation, should a developer have to worry about such things? Even the seemingly clear TRUE/FALSE Boolean values are open to interpretation. If you generally do not want the trace in action, then TRUE should mean "keep it off." If you are often interested in the output of the trace, you would most naturally conclude that TRUE means "show the trace."

A completely different technique is to provide two different programs to turn the trace on and off. The names of the programs themselves would make it very clear what they did. You don't have to worry about getting a literal value wrong. If you type in the wrong program name, the runtime engine will inform you immediately of the error.

I can employ a very generic naming convention for this pair of on/off procedures as follows:

PROCEDURE turn_on;
PROCEDURE turn_off;

As an alternative, I could use names that describe the type of trace being provided. This is especially important when more than one trace is provided in the same package.

In PLVdyn, I opted for the less generic style and so provide these two procedures in the package specification:

PROCEDURE showsql;
PROCEDURE noshowsql;

With these programs in place, I could turn on my trace in SQL*Plus as follows (ssoo.sql is a PL/Vision script that sets SERVEROUTPUT to ON, enabling the DBMS_OUTPUT package in SQL*Plus):

SQL> @ssoo
SQL> execute PLVdyn.showsql;

The third program of the package trace is a function that lets me know the current status of the PLVdyn trace facility. In the PLVdyn package, I offer the showing_sql function. This program returns TRUE if the trace is turned on, FALSE otherwise:


The PLVdyn uses this function (shown later in this section) when trying to determine whether or not the SQL should be shown. Even the package body respects the interface of the toggle and uses the function instead of a direct reference to the private variable.

The showing_sql function also provides a sense of completeness to the interface for the trace facility. A developer using PLVdyn can remain within the API of the package to obtain all the information she needs to use the trace and get the most out of the package.

The full implementation of the trace facility in PLVdyn even goes a bit further than you have seen so far. The header for the showsql procedure is:

PROCEDURE showsql (start_with_in IN VARCHAR2 := NULL);

You can, in other words, provide a string to showsql to indicate the point in the SQL from which you want to view the text. You could ask to see, for example, everything after the WHERE keyword by calling showsql as follows:

SQL> exec PLVdyn.showsql ('where');

This additional flexibility can come in handy when you don't want to have to read your way through a long, complicated SQL statement. It's quite easy to provide additional functionality to a package window once it has been put in place. My first implementation of showsql did not support this "start with" argument nor did it display long strings very gracefully. I was able to add all of this functionality incrementally as I identified the need.

2.7.3 Implementing the Window

Now that the interface to the window has been defined, I need to implement the code that will fill that window with data. One of the biggest challenges in crafting a trace facility in a package like PLVdyn is to figure out where to put the trace. PLVdyn is a big package, offering many different high-level operators to perform dynamic SQL.

I did not want to have to add calls to DBMS_OUTPUT all over the package. That would make it more difficult to maintain and enhance. So I analyzed the way the package (and dynamic SQL) works and found my attention drawn back continually to the open_and_parse function. Before you can execute a SQL statement, you have to open a cursor and then parse the SQL.

The open_and_parse function was one of the first programs I created in PLVdyn, and it is used by all other programs before they move on to their specific dynamic tasks. As a result, open_and_parse acts as a kind of gateway into the rest of the package. I reasoned, therefore, that if I added the trace capability to open_and_parse, I could then make the trace available to the entire package. Now that's a payoff from earlier modularization! Here is the body of open_and_parse:

FUNCTION open_and_parse (string_in IN VARCHAR2,
   display_dynamic_sql (string_in);
   DBMS_SQL.PARSE (cur, string_in, mode_in);
   RETURN cur;

As you can see, the display_dynamic_sql procedure intercepts the string that is going to be parsed by the built-in PARSE procedure. A simplified version of the display program is shown below:

PROCEDURE display_dynamic_sql (string_in IN VARCHAR2)
   IF showing_sql
      PLVprs.display_wrap ('PLVdyn: ' || v_string, 60);
   END IF;

Notice that display_dynamic_sql only displays information when showing_sql returns TRUE. It also takes advantage of the PLVprs.display_wrap procedure to show long SQL statements in paragraph form wrapped at a line size of 60 columns.

The open_and_parse program is called six times in PLVdyn. Actually, as I wrote this section, I had been thinking that the count would be even higher. It turns out that any of the programs that call open_and_parse are, in turn, called by other PLVdyn modules, keeping the direct references to open_and_parse from exploding. The display_dynamic_sql program, on the other hand, is called just once. When I want to upgrade or change the functionality of my trace, I can go to this one program and make all the changes.

The way I was able to implement the trace in PLVdyn is a best-case scenario. The requirement in dynamic SQL to parse your SQL statement, combined with my initial modularization and reuse of open_and_parse, offered an easy way to put the trace in place. In other PL/Vision packages and your own as well, you may need to include calls to your trace display mechanism more than once. That's fine, as long as you do create a separate procedure to display the information (do not just call DBMS_OUTPUT.PUT_LINE directly in your package) and as long as you minimize the number of repetitions of the program.

2.7.4 Summarizing the Window Technique

The trace facility of PLVdyn illustrates some important principles of both generic package structure and high-quality reusable code (see Figure 2.1). First, the public-private nature of the package allows me to construct a window into PLVdyn. This window offers a very controlled glimpse into the interior of the package. I let developers view the dynamic SQL string, but they can't look at or do anything else. This level of control allows Oracle to give us all of those wonderful built-in packages like DBMS_SQL and DBMS_PIPE. And it lets developers provide reusable PL/SQL components to other developers without fearing corruption of internal data structures.

Figure 2.1: Window/trace in PLVdyn

Figure 2.1

The three elements of the interface to the window are:

  1. A procedure to open the window and turn on the trace

  2. A procedure to close the window and turn off the trace

  3. A function that returns TRUE if the window is open, FALSE otherwise

Your package can have more than one window, in which case you will want to have a distinct triumvirate of programs for each trace (multiple toggles, in other words). If you have a number of different kinds of windows, you may also want to build a master switch that turns on and off all of the windows at once. The PLVgen usemin and usemax procedures are good examples of this meta-toggle for multiple flags in the package.

As you build more and more sophisticated packages, you will find yourself building code in multiple layers that interact in ways mysterious to normal human beings. The windowing technique illustrated by PLVdyn.showsql will be absolutely critical to making your packages widely accessible and usable. If you do not provide clearly defined windows into your inner workings, there is a good chance that developers will first be baffled and then become frustrated. The end result is that they will not use your packages.

Previous: 2.6 Building Flexibility Into Your PackagesAdvanced Oracle PL/SQL Programming with PackagesNext: 2.8 Overloading for Smart Packages
2.6 Building Flexibility Into Your PackagesBook Index2.8 Overloading for Smart Packages

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
This HTML Help has been published using the chm2web software.