17. PLVhlp: Online Help for PL/SQL Programs

Contents:
Who Needs Online Help?
Current Sources of Information
What Is "Online Help" for Stored Code?
Using PLVhlp
Implementing PLVhlp

This chapter explores the PLVhlp (PL/Vision HeLP) package. You can use PLVhlp to provide online help for your own PL/SQL programs. Just imagine: If you or someone else in your development team forgets how to call a program or can't remember the name of the function to calculate the total something or other in the sales_analysis package, you can simply type:

SQL> exec sales_analysis.help

and find text scrolling across the screen, one page at a time, giving you just the information you need. Why, you might almost be able to actually share PL/SQL code across the enterprise in a practical way!

In the following sections, you will learn how to use PLVhlp, both in terms of creating text that can be made available for online help, and in terms of retrieving and displaying the online help text. I then take you step by step through the thought process and implementation behind PLVhlp.

17.1 Who Needs Online Help?

PL/Vision contains many packages, with many programs in each package. Despite its complexity, since I am the author of PL/Vision I should be intimately familiar with all aspects of the product. So you can imagine my frustration when I can't remember precisely how to call one of my own packaged programs, and have to go scrounging through the source code to figure it out. I fume to myself: "You wrote this stuff! Can't you remember what it does and how it does it?" But the sad fact is that I cannot remember all the finer nuances of my finer creations. As a result, valuable moments are lost from my life.

What I really need is online help so that when I am confused, I can type in a short, easy-to-remember command (along the lines of HELP!) and get the information I need.

To give you an understanding of my frustration, I offer the following transcript of a session with SQL*Plus (words in italics reflect the inner thoughts of yours truly trying to get something done).

I fire up SQL*Plus. On the one hand, I sigh at having to deal with this command-line, non-GUI interface. On the other hand, I know that this is the fastest, leanest environment in which to compile and run PL/SQL programs. Here I go...

SQL>

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. I guess it's not called getnext after all. Well, what the heck is it called? Oh yeah! It's get_next_token .

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

#$%!! No, that's not correct either! Now I am really frustrated. Sigh... guess it is time to go into Codewright, open up the source code and take a look around... well, I'm lucky to at least have ready access to my code...

Have you ever had this kind of problem? The database is jam-packed with all sorts of goodies, but how are you supposed to know what's there and what it's good for? Stored code is wonderful, but there is no easy way at the moment to view that stored source code -- a necessary step to figure out how to call a particular program or understand what happens when you do run the program. This is most crucial when working with packages. In this case, the package specification is supposed to contain all the information you will ever need to use that package, but how do you find and view that specification?