17.2 Current Sources of Information

Chapter 17
PLVhlp: Online Help for PL/SQL Programs

 

---

OK, you've got the DESCRIBE command in SQL*Plus. This command displays the parameters of a stored program (either standalone or package-based). For example, if I want to know about the call interface to my PLVvu.code procedure, this is what I do:

SQL> desc PLVvu.code PROCEDURE PLVvu.code  Argument Name   Type           In/Out Default?  --------------- -------------- ------ --------  NAME_IN         VARCHAR2       IN     DEFAULT  START_IN        NUMBER(38)     IN     DEFAULT  END_IN          NUMBER(38)     IN     DEFAULT  HEADER_IN       VARCHAR2       IN     DEFAULT  TYPE_IN         VARCHAR2       IN     DEFAULT

The DESCRIBE command even tells me the return datatype of a function:

SQL> desc PLVtkn.is_keyword FUNCTION PLVtkn.is_keyword RETURNS BOOLEAN  Argument Name   Type           In/Out Default?  --------------- -------------- ------ --------  TOKEN_IN        VARCHAR        IN  TYPE_IN         VARCHAR2       IN     DEFAULT

The DESCRIBE command, by the way, makes use of the DESCRIBE_PROCEDURE procedure of the builtin DBMS_DESCRIBE package. This program returns a program's arguments in a series of PL/SQL tables. It's a great utility, except it doesn't tell me anything about what the program does -- only how to call it. Furthermore, you need to know the name of the program to use DESCRIBE. The DESCRIBE command does not, in other words, offer online documentation or help for this code.

Another option is to view the source code (assuming that you have access to it, which is far from certain). The PLVvu package provides the code and code_after procedures for just this purpose. For example, I can view the first ten lines of the PLVvu package itself as follows:

SQL> exec PLVvu.code ('b:PLVvu', 1, 10); ----------------------------------------------------------------------- Code for PACKAGE BODY PLVVU ----------------------------------------------------------------------- Line#  Source -----------------------------------------------------------------------     1 PACKAGE BODY PLVvu     2 IS     3    c_product_header VARCHAR2(30) := 'PL/Vision';     4    c_linelen INTEGER := 77;     5    v_last_border BOOLEAN := FALSE;     6    v_overlap INTEGER := c_overlap;     7    /*--------------- Private Modules -----------------*/     8    PROCEDURE disp_border     9       (line_in IN INTEGER := NULL,    10        err_line_in IN INTEGER := NULL,

Unfortunately, both DESCRIBE and the PLVvu.code approach are still fairly crude and heavy-handed solutions to a basic problem in PL/SQL: the lack of an online help facility for one's code.

In the remainder of this chapter I present an architecture (and, of course, a PL/SQL package) for implementing online help for PL/SQL programs. The resulting PLVhlp package may not the most elegant approach one would want to take, but it has lots of potential for improving the lot of developers.