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:

• How should the user ask to turn the trace on and off?

• What other information can I provide to or ask from the user?

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:

FUNCTION showing_sql RETURN BOOLEAN;

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,
    mode_in IN INTEGER := DBMS_SQL.NATIVE) RETURN INTEGER
IS
   cur INTEGER := DBMS_SQL.OPEN_CURSOR;
BEGIN
   display_dynamic_sql (string_in);
   DBMS_SQL.PARSE (cur, string_in, mode_in);
   RETURN cur;
END;

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)
IS
BEGIN
   IF showing_sql
   THEN
      PLVprs.display_wrap ('PLVdyn: ' || v_string, 60);
   END IF;
END;

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

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.