17.3 What Is "Online Help" for Stored Code?

First of all, let me make a distinction and set some scope for this chapter: I am not talking about online help for PL/SQL itself (such as "what is the syntax of the OPEN statement?"). Instead, I want to address how to provide online help for PL/SQL programs that you or I might write and then make available to others. So when I say "online help for PL/SQL," I mean online instructions on how to use custom-built and prebuilt PL/SQL programs that have been stored in a database and on which you have execute authority.[1]

[1] You could, by the way, also use this package and techniques to provide online help for builtins of the PL/SQL language.

Let's now run through the same scenario I presented at the beginning of the chapter, this time with online help for PL/SQL available.

All right, now I want to parse a string into separate tokens. Let's see, that's gotta be the PLVlex package and the getnext function.

SQL> VARIABLE toke VARCHAR2(30);
SQL> exec :toke := PLVlex.getnext ('this is it');
begin :toke := PLVlex.getnext ('this is it'); end;
 *
ERROR at line 1:
ORA-06550: line 1, column 23:
PLS-00302: component 'GETNEXT' must be declared
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored

Well, that didn't work. Admit it, Steven, you forgot how to use your own code. Time to get some help.

SQL> exec PLVlex.help
Help for PLVLEX
Overview: provides lexical analysis and functions for strings.
===================================================================
Element - Description
===================================================================
FUNCTION is_delimiter - Returns TRUE if string is a delimiter.
PROCEDURE get_next_token - Returns next token in string.
PROCEDURE get_next_atomic - Returns next atomic in string.

Now I know what's in the package. What I'd really like are examples of the ways these programs can be used.

SQL> @examples PLVlex
Examples for PLVLEX

Here is a call to get_next_token as used by PLVcase,
which UPPER-lower cases PL/SQL programs:

   LOOP
      PLVlex.get_next_token
        (text, curr_pos, token, next_pos, FALSE, text_len, TRUE);

      EXIT WHEN v_token IS NULL OR line_in.pos > line_in.len;

   END LOOP;

The FALSE indicates that I do not want to skip over spaces. This
maintains the program's indentation. The TRUE indicates that I want
qualified names (X.Y.Z) to be returned as one token.

I am now ready to roll. That is just the information I needed to move into high gear!

That is the kind of help I would love to be able to get from a PL/SQL environment. Of course, the help itself is only as good as the text you enter. In addition, you need to at least know the name of the package you want to be using... well, I take that back. After all, once the help mechanism is in place, you could provide a very high-level "table of contents" or index into your programs, as I show below.

SQL> @help
Overview of PL/Vision

PL/Vision is a development accelerator for PL/SQL programmers.
It is made up of a set of packages which you can use as
plug-and-play components in your own applications. Here is a
quick overview of some of the available packages:

PLVdyn - performs dynamic SQL and PL/SQL operations.
PLVexc - High-level exception handling capabilities.
PLVlex - Lexical analysis and parsing of strings.
PLVlog - Generic log mechanism.
PLVvu - View stored code and compile errors.

At this point I imagine you understand the idea -- and I hope you like it. If not, skip the rest of this chapter. If so, I first provide the basic information ("user's guide") you need to use the PLVhlp package. After that I move on to the underlying principles, architecture, and implementation of online help for your PL/SQL programs so that you can get a better understanding of how I went about constructing this utility.