17.5.3.1 Functions to scan USER_SOURCE

The startcmnt and endcmnt functions both scan the USER_SOURCE view for specific lines.[ 2 ] Below is the header for the startcmnt function. (By the way, it might seem that I worked all this out in advance. The reality is that I am developing this implementation as I write this chapter. My production, PL/Vision-based implementation of online help, uses a completely different approach.)

[2] All of the code described in this section is contained in the showhelp.all file in the text subdirectory.

FUNCTION startcmnt
   (name_in IN VARCHAR2,
    type_in IN VARCHAR2,
    nth_in IN INTEGER := 1)
RETURN INTEGER;

Notice that I have three arguments listed, yet only two arguments were provided to the calls to startcmnt inside the SQL statement. As I started to build this function, I realized that it would probably be reasonable to ask for the third comment block in a program. I have added the parameter, but supplied it with a default value that makes the online SQL work properly.

I make one important assumption to ease implementation in startcmnt : the comment blocks for which I search always have the start-comment symbol ( /* ) on a new line. I take advantage of this rule by performing an LTRIM on the text I search in the view. Here is the cursor I set up to find the start of a comment block:

CURSOR line_cur 
IS
   SELECT line 
     FROM USER_SOURCE
    WHERE name = UPPER (name_in) 
      AND type = UPPER (type_in)
      AND LTRIM (text) LIKE '/*%';

Once I have the cursor in place, the implementation is fairly straightforward. I open a cursor into the USER_SOURCE view for all lines of text for the specified program that start with /* . Then I fetch from that cursor until the %ROWCOUNT matches the nth_in argument value (thereby reaching the n th comment block in the program) or until I run out of records. Here is the very concise loop:

OPEN line_cur;
LOOP
   FETCH line_cur INTO line_rec;

   IF line_cur%NOTFOUND
   THEN
      retval := 0;
   ELSIF line_cur%ROWCOUNT = nth_in
   THEN
      retval := line_rec.line;
   END IF;

   EXIT WHEN retval IS NOT NULL;
END LOOP;
CLOSE line_cur;

Should I use a cursor FOR loop in startcmnt ? I could then avoid the explicit open, fetch, and close. That would not, however, be a good choice here, since I perform a conditional exit out of the loop. You should only use a cursor FOR loop if you truly are going to touch every record retrieved by the cursor.

The endcmnt function is virtually identical to startcmnt (see the showhelp.all file). The only differences show up in the SQL statement of the explicit cursor. This is the endcmnt cursor:

CURSOR line_cur
IS
   SELECT line 
     FROM USER_SOURCE
    WHERE name = UPPER (name_in) 
      AND type = UPPER (type_in)
      AND text LIKE '%*/' || CHR(10);

There are two differences:

1. I need to look for a match on %*/ instead of /*% since I am looking for the termination of a comment, rather than its beginning.

2. I append a CHR(10) or newline character in my match. Why do I do this? It turns out that there is a newline character at the end of very line of code stored in the database (at least when SQL*Plus is used!). There are a number of ways one can handle this detail. I simply append the CHR(10) to my search criteria. You could also RTRIM from text.

This complication of the newline character is a good example of how a seemingly simple task can become more complicated as you deal with the reality of an implementation. PL/SQL and Oracle software in general is full of these kinds of surprises. Everything you ever do is always going to be more complicated than you first imagined, and it is a good idea to plan for this in your work

Challenge for the reader: do I really need two different functions, when they are so alike? I suggest that you practice your modularization skills by transforming startcmnt and endcmnt into a single function, thereby reducing your code volume and easing maintenance.

17.5.5.1 Setting the current object

Before I can use PLVio to read from the USER_SOURCE data dictionary view, I must define the current object with PLVobj. The first line of the show procedure's body saves the current settings for the PLVobj current object. I do this so that if PLVobj were being used, I could restore the current object when done showing help text.

Then I call setcurr to set the current object. I pass in to setcurr the module that was provided in the call to show:

  PLVobj.setcurr (context_in);

Now the PLVio program units can be called.

17.5.5.2 Setting up PLVio to read help text

First, I inform PLVio that I will be reading from the USER_SOURCE view:

PLVio.usrc;

Then I initialize the source so that I only read the rows in USER_SOURCE for the current object that correspond to the specified help topic or part:

PLVio.initsrc (help_start (part_in), help_end (part_in));

The first argument to PLVio.initsrc passes the string that should be the first line read. The second argument contains the string that signals the end of the help text. The two functions, help_start and help_end , are private to the PLVhlp body; they are not listed in the specification at all. These are used to format the start and end strings of the help text block, doing little more than attach the comment markers to the specified context string:

FUNCTION help_start (topic_in IN VARCHAR2 := NULL)
   RETURN VARCHAR2
IS
BEGIN
   RETURN '/*' || topic_in;
END;

FUNCTION help_end (topic_in IN VARCHAR2 := NULL)
   RETURN VARCHAR2
IS
BEGIN
   RETURN topic_in || '*/';
END;

I create these little functions to hide my particular implementation of the start and end indicators of a comment block. I may well want to change my approach in subsequent implementations. By using these functions, I only have to make the changes there and not scattered throughout my package.

17.5.5.3 Moving the help text to a PL/SQL table

Now that the PLVio source repository has been set and initialized, I can set the target repository and then transfer all help text to that target. This call to settrg tells PLVio that I want all calls to PLVio.put_line to put text in the PLVio target PL/SQL table:

PLVio.settrg (PLV.pstab);

Then I call the high-level src2trg procedure that simply batch transfers all rows of help text from USER_SOURCE to the PLVio target:

PLVio.src2trg;

This procedure hides all the get_line and put_line logic of PLVio and lets me very easily move the PL/SQL source code to my choice of target for further manipulation.

17.5.5.4 Showing the first page

There are two steps involved in showing the first page of help text: set page management variables and then display the text. This first statement:

set_more (2, 0);

sets the values of variables to control the behavior of the PLVhlp more program. In this case, the call to set_more is saying: start at line 2 (the first line is the designator, such as /*HELP , and can be ignored) and display the first page of text.

The program that actually generates the output is the more procedure, which is called as the last line in the PLVhlp.show procedure.

17.5.5.5 Leveraging and enhancing PL/Vision

The show procedure offers an excellent example of how I am able to leverage the prebuilt packages of PL/Vision to very quickly assemble new and often richly featured programs. This shows that I have reached a "critical mass" of code in PL/Vision; my earlier investment in building reusable, low-level layers of code is paying off.

Even at critical mass, however, I still find myself enhancing the base layers of code. For example, I call src2trg in PLVhlp.show to move en masse all the rows of help text to the PLVio PL/SQL table. When I was writing PlVhlp.show , src2trg hadn't yet been written. As I confronted the task at hand in PLVhlp.show , I realized that I did not want to have to bother with all the internals of PLVio. I simply need to move all the rows out of USER_SOURCE and into the PL/SQL table so I could display the text in a highly controlled fashion.

So I stopped my development in PLVhlp and shifted gears into PLVio. I built src2trg , tested it, and then used it in the PLVhlp.show procedure. By doing so, I not only produced the functionality I needed in PLVhlp, but also expanded the capabilities of PLVio.

As I've mentioned before, this process is typical of the way I have been developing my code over the last year. Rather than work on any one package at a time, I find myself simultaneously enhancing several different packages. As I encounter the need for a new program, I check to see if there is a package already in PL/Vision into which this program should logically fall. If so, I add to that package. If not, I create a new package. With this approach, I constantly increase the amount of reusable code in my library and achieve the broadest possible impact with each new development.

Now that you have seen how I identify, read, and store my help text, let's move on to figuring out how best to display that help text.

17.5.6.1 A futile first effort

What's an obsessed developer to do? One idea I had was this: Inside the cursor FOR loop of the hlp procedure, which reads and displays a line of text, call the DBMS_LOCK. SLEEP program to pause execution of the program for perhaps 10 seconds every 25 lines. This gives the developer time to read the help text. And you wouldn't even have to press Enter to continue. It would figure it out all by itself! A loop that paused every 10 rows would look like this:

FOR text_rec IN text_cur (v_name, v_type)
LOOP
   DBMS_OUTPUT.PUT_LINE 
     (RTRIM (text_rec.text, CHR(10)));
   IF MOD (text_cur%ROWCOUNT, 10) = 0
   THEN
      DBMS_LOCK.SLEEP (10);
   END IF;
END LOOP;

Is this a clever use of the SLEEP program or what? I strutted like a peacock in front of my computer as I set up this implementation. Then it was time to test. So I ran the modified hlp program against a block of 100 lines of text. Normally it took about three seconds to display this text. How long do you think I had to wait before I saw the first ten lines of code? One second? Three seconds? Would you believe one minute and forty-three seconds? Yes, that's right: I waited 103 long, bewildering seconds -- and then all 100 lines of text blew by me without a single pause. What had happened?

Everything worked just the way I'd asked it to -- I just hadn't fully understand what it was I had asked for. The hlp program did write ten lines to the DBMS_OUTPUT buffer and then did go to sleep for ten seconds -- ten times straight. Remember: you don't ever see any output at all from DBMS_OUTPUT until the entire program terminates and returns control to the host environment, be it SQL*Plus or Procedure Builder.

My conclusion from this fruitless effort? If I was going to interrupt successfully the output from a PL/SQL program, I would have to actually stop that program so that it could dump its buffer -- and then run it again to display more text. At this point, then, there was no doubt that I would need to move to a package-based implementation. Why? Because I was talking about executing more than one program that would share information about the help text (the text itself, the last row displayed, etc.). Only the package allows me to create persistent, memory-based data.

17.5.6.2 A successful pause architecture for PL/SQL

There were two basic architectures I could use to display n lines of help text before terminating execution of the PLVhlp.show program:

1. Use a package-based cursor. If I define the cursor that accesses USER_SOURCE at the package level, it remains open in between calls to programs that fetch from the cursor and display the text. The PLVhlp.show program would open the cursor, fetch the first n rows, display them, and terminate. When PLVhlp.more is called, it just keeps fetching the next set of records from the cursor.

2. Transfer all lines of help text into a PL/SQL table and then display the next n rows each time PLVhlp.more is called.

In both cases I take advantage of a central feature of PL/SQL -based data structures: they persist for the duration of an Oracle session. The cursor stays open, and the PL/SQL table remains populated, in between program calls. The first, cursor-centered approach is simpler since it does not involve an intermediate PL/SQL table. It is probably the technique I would have used had I not already built PLVobj, PLVio, and PLVtab. Given these prebuilt components, however, it was a no-brainer for me to pursue the PL/SQL table solution.

Having gone with the PL/SQL table, however, another significant advantage became obvious: help text deposited in this data structure could be managed in a very flexible and efficient way. The text could, for example, be passed on to another environment for display, such as Oracle Forms or a third-party PL/SQL development environment.

17.5.6.3 Implementing more

The more procedure of PLVhlp, shown below, contains the logic necessary to display a page of rows of the PL/SQL table, as shown in Example 17.1 :

As with PLVhlp.show , the more program is short and relies heavily on the PLVobj and PLVio packages. It also makes two calls to the p.l procedure. As I've described in Chapter 7 , the p package and its l procedure provide a substitute for DBMS_OUTPUT.PUT_LINE that requires much less typing and offers additional functionality (such as displaying rather than ignoring NULL text and automatically substringing the text to a maximum length of 255 bytes to avoid VALUE_ERROR exceptions.)

Now let's go examine the more program, so that you can fully understand the implementation of a PL/SQL -based, page-pausing mechanism. There are three private package variables used to manage behavior in the PLVhlp.more package, as listed below:

v_more	TRUE if there are more lines of help text to display.
v_startrow	The first row in the PL/SQL table of the next page of text.
v_endrow	The last row in the PL/SQL table of the next page of text.

At the very start of more , I check the value of v_more . If it is FALSE, I display an appropriate message. If v_more evaluates to TRUE, then I display the next page of text using the PLVio package (lines 5 through 7 of Example 17.1 ):

PLVio.disptrg ('Help for ' || PLVobj.currname, v_startrow, v_endrow);

The disptrg or "display target" program displays the contents of the PL/SQL table maintained as a target by the PLVio package (I never have to declare it or directly manipulate its contents; the PLVio package takes responsibility for this work). I provide a header and a range of rows to display. This is another good example of how the abstraction in my lower-level packages makes programming a breeze.

17.5.6.4 Maintaining the current page variables

Now that I have displayed this latest page, I reset the triad of variables for the next call to more . First, I check to see if there are any more rows. I know I am done when the end row matches the second-to-last row in the target table. I ignore the very last line, because it is simply the comment marker ( HELP*/ , for example). I then restore the current object in PLVobj and set v_more to FALSE:

IF v_endrow = PLVio.target_row-1
THEN
   PLVobj.restore_object;
   v_more := FALSE;

If, on the other hand, I have not displayed all rows, then I display a message indicating that there is more to come (when and if the user executes PLVhlp.more again) and then call the set_more program to set up my variables:

p.l ('...more...');
set_more (v_endrow + 1, v_endrow);

The set_more program is also not very complicated:

PROCEDURE set_more
   (start_in IN INTEGER, end_in IN INTEGER)
IS
BEGIN
   v_startrow := start_in; 
   v_endrow :=  
      LEAST (v_pagesize + end_in, PLVio.target_row-1); 
   v_more := TRUE;
END set_more;

Translation: the start row is set to the next row after the current end row. The end row is set to the smaller of these two values: (a) the current end row plus another page's worth of lines or (b) the last row to be displayed in the PL/SQL table. Finally, v_more is set to TRUE just to make sure.

To help you understand how the page-management variables shift the rows being displayed, let's step through a scenario. Suppose that I have set the pagesize to 10 lines ( v_pagesize ) and I have 24 lines of text in my help section for the PLVprs package (PLV io.target_row ). When I call PLVprs.help , the initializing call to set_more sets the page-management variables as follows:

set_more (2, 0);                  v_startrow := 2
                                  v_endrow := LEAST (10+0, 24-1) = 10

As a result, lines 2 through 10 are displayed on the screen. Then set_more is called again with the following arguments and results:

set_more (v_startrow, v_endrow);  v_startrow := v_endrow + 1 = 10 + 1 = 11
                                  v_endrow : = LEAST (10+10, 24-1) = 20

When I call PLVhlp.more to see the next page of help, lines 11 through 20 are displayed and set_more is then called with these results:

set_more (v_startrow, v_endrow);  v_startrow := v_endrow + 1 = 20 + 1 = 21
                                  v_endrow : = LEAST (10+20, 24-1) = 23

And since PLVhlp tells me so, I call PLVhlp.more to see the next page of help, lines 21 through 23; set_more is then called. This time around, v_endrow does equal the last line of text to be displayed, so v_more is set to FALSE, the PLVobj current module values are restored, and PLVhlp.more is, effectively, disabled.

This PL/SQL table-based technique for page-pausing is not as easy to use as the one built into SQL*Plus. With SQL*Plus, you simply press the Enter key to see the next page. With PLVhlp, you have to type:

SQL> exec PLVhlp.more

or, if you create a standalone procedure as I have, simply:

SQL> exec more

or, if you create a SQL script that performs the above exec , you could even simplify that to:

SQL> @more

Regardless, it's more work than what you do with SQL*Plus, but considering that it is an add-on layer of code and functionality, it's not too odious. Of course, if you do not use SQL*Plus, then we can skip the comparison and simply celebrate the ability to build such utilities in PL/SQL .