|
Chapter 17 PLVhlp: Online Help for PL/SQL Programs |
|
I hope that you put PLVhlp to use in your environment; the earlier sections in this chapter should provide a clear guide to doing so. My objective with this book, however, is to also help you develop a philosophy and perspective on your PL/SQL development so that you can build your own utilities like PLVhlp. The rest of this chapter explores the issues, technical challenges, and solutions I encountered in my implementation of PLVhlp.
To show the iterative, spiraling nature of software development, I will step through the implementation of PLVhlp in two stages. In stage one, I build a relatively simple, but effective working model of a script to deliver online help. This version is based on SQL and works only in the SQL*Plus environment. In the second stage, I build a more comprehensive, flexible, and feature-rich online help architecture based on PL/SQL packages (PLVhlp, of course).
The great thing about PL/SQL is that you can use it darn near anywhere: UNIX servers in SQL*Plus, Oracle Forms, home pages on the Internet... you name it and PL/SQL rears its pretty face. Furthermore, the scope of PL/SQL will also almost certainly expand over the years as well, as it becomes one of the key enabling technologies for application partitioning in n-tier environments (now it's three-tier, but why not four- or five-tier? The more layers the better?).
The ubiquitousness of PL/SQL implies to me at this point in time that I want an online help approach to be, shall we say, "lowest common denominator." The "lowest" of LCD refers to (a) the execution environment, (b) the location of the help text in one's environment, and (c) the methodology for retrieving and displaying the help text.
As of the publication of this book, the most common execution environment, and the most generic, is SQL*Plus. The lowest, or deepest, location in an Oracle environment is the database. The most widely available delivery mechanism for PL/SQL online help is... PL/SQL !
Given this analysis, here is my vision of how an online help system for PL/SQL would work: provide a programmatic interface (via a package) to help text. This interface (a set of procedures and functions) could be called from within SQL*Plus with an execute statement. But, heck, since it's based on PL/SQL , you could also build a frontend in PowerBuilder or Oracle Forms to access this same information. You could also execute calls to the interface from within a tool like Procedure Builder. For me, however, the most important baseline is to construct that procedural interface and make it work in good, old SQL*Plus.
Let's talk about the help text itself. This is actually the single greatest challenge in building an online help system; someone has to take the time and make the effort to enter text. I know that I have very little patience for this kind of polishing effort, so at a minimum I believe that it is critical that I be able to enter my help text only once and yet have it satisfy the following needs:
Function as inline documentation. The text should be useful to developers who are enhancing and maintaining the source code itself. In this situation, developers do not access this text via a programmatic interface. Rather, they edit the code, and (if they are especially methodical) they simultaneously update any help text/documentation that is affected by their changes.
Be available for online help. The same text should also be accessible and readable so that it can be displayed online to users of the code.
The only way to satisfy both of these needs with one version of text is to place the documentation directly within the definition of the program. This text is then stored in the database along with the program. That text is then available to a developer and also to a user (through the programmatic interface).
To summarize my principles of online help:
Centralize help text; avoid redundant entry of text. Do this by storing help information with the program itself, in the data dictionary.
Make the help text accessible from any environment in which PL/SQL programs can be executed. In other words, implement the delivery mechanism for help through PL/SQL programs.
Working from these principles, I first examine how my help text is stored in the data dictionary and how I can best retrieve it. Then I build a working prototype of a module that delivers online help for PL/SQL programs.
When you CREATE OR REPLACE a program (procedure, function, or package) into the Oracle database, the source code is saved to the SYS.SOURCE$ table. You can view the contents of this table for all of your stored programs by accessing USER_SOURCE view. The structure of this view is as follows:
SQL> desc user_source Column Column Name Null? Type -------------- -------- ------------- NAME NOT NULL VARCHAR2(30) TYPE VARCHAR2(12) LINE NOT NULL NUMBER TEXT VARCHAR2(2000)
The NAME column contains the name of the object. The name is always stored in upper case unless you surround the name of your program in double quotation marks at creation time. I assume in my help implementation that you don't do this and that your program name is always upper-cased. The TYPE is a string describing the type of source code, either PROCEDURE, FUNCTION, PACKAGE, or PACKAGE BODY ( always upper case). The LINE is the line number and the TEXT is the line of text. Notice that a line of text may be up to 2000 bytes in length.
Suppose that I issue the following command in SQL*Plus:
SQL> create or replace PROCEDURE temp IS 2 BEGIN 3 DBMS_OUTPUT.PUT_LINE ('hello world'); 4 END; 5 /
My program will be stored in the database. The following query then retrieves the source code for display:
SQL> SELECT text FROM USER_SOURCE 2 WHERE name = 'TEMP' AND type = 'PROCEDURE' 3 ORDER BY line; TEXT --------------------------------------------------- PROCEDURE temp IS BEGIN DBMS_OUTPUT.PUT_LINE ('hello world'); END;
Notice that the CREATE OR REPLACE and / are not stored. These are part of the SQL*Plus command environment only.
Now suppose that I also include a comment in the code:
SQL> create or replace PROCEDURE temp IS 2 BEGIN 3 /* Send message to standard output. */ 4 DBMS_OUTPUT.PUT_LINE ('hello world'); 5 END; 6 /
Then my query could just as easily extract only that line of text, as shown in each of the two SQL statements below. The first query returns any text that starts with a comment. The second query retrieves only the third line.
SELECT text FROM USER_SOURCE WHERE name = 'TEMP' AND type = 'PROCEDURE' AND LTRIM (text) LIKE '/*' ORDER BY line; SELECT text FROM USER_SOURCE WHERE name = 'TEMP' AND type = 'PROCEDURE' AND line = 3;
In both cases, I will have retrieved my comment. This capability forms the core of the technique for online help in PL/SQL programs. Comments, which contain documentation about the program and, therefore, potential help text as well, can be stored and manipulated just as actual lines of code can be.
From here on in, it's all in the details -- but wait until you see these details!
It's one thing to write a query to extract a line that starts with a comment marker. It's quite another challenge to generalize that query into a SELECT statement that selectively retrieves only those lines related to a specific topic. Suppose, for example, that my source code looks like this:
PROCEDURE sayhi IS /* || Overview: the sayhi procedure uses the || DBMS_OUTPUT builtin to say hello || to the world. */ BEGIN DBMS_OUTPUT.PUT_LINE ('hello world'); END;
My task is now to write a query that displays only the comment information. How is that range of information defined? In this simple case, I want to display all lines after the first instance of /* and up to the very next first occurrence of */ after that line. In pseudo- SQL , I have something like this:
SELECT text FROM USER_SOURCE WHERE name = 'SAYHI' AND type = 'PROCEDURE' AND line BETWEEN startcmnt ('sayhi', 'PROCEDURE') AND endcmnt ('sayhi' 'PROCEDURE') ORDER BY line;
Actually, this isn't even pseudo- SQL . I am simply calling PL/SQL functions ( startcmnt and endcmnt ) from within a SQL statement, which you can and should do from Oracle Server 7.1 and onwards. This is a form of top-down design, because at this time I'm not really sure what these functions actually need to do in order to pass back their line numbers. The SQL statement looks right, which means that I am ready to take it to the next level of detail.
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:
I need to look for a match on %*/ instead of /*% since I am looking for the termination of a comment, rather than its beginning.
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.
Once I have both of the PL/SQL functions in place, I can return to my original SQL statement and enhance it into a generic SQL*Plus script to deliver help. To achieve this change, I remove all references to specific program names and types to SQL*Plus parameters (numbers of strings prefixed by the & character). I also add some SET commands to limit output from the SELECT statement to just my help text.
The following script is stored in showhelp.sql :
SET FEEDBACK OFF SET VERIFY OFF SELECT text FROM USER_SOURCE WHERE name = UPPER ('&1') AND type = UPPER ('&2') AND line BETWEEN startcmnt ('&1', '&2') AND endcmnt ('&1', '&2') ORDER BY line;
Notice that I automatically perform an uppercase conversion on the name and type. I am simply enforcing an assumption of my utility. By calling UPPER, I liberate the user from having to remember this kind of detail.
Now if I run this script in SQL*Plus, I get my online help:
SQL> @showhelp sayhi procedure /* || Overview: the sayhi procedure uses the || DBMS_OUTPUT builtin to say hello || to the world. */
With showhelp , I have in place a rudimentary prototype of a help-deliverer for PL/SQL programs. It acts as a proof of concept for my technique, but it isn't really a full featured help system. There are a number of weaknesses in this implementation that I want to address:
It is an SQL script specific to SQL*Plus. It cannot be executed from within PL/SQL at all and would have to be modified even to execute in any other development environment as SQL (i.e., a non-SQL*Plus frontend to SQL ).
The showhelp procedure isn't very flexible. It assumes that any comment is help, which may not really be acceptable. You'd want a way to clearly identify comments for use as online help.
The showhelp procedure requires lots of typing by the user. The more you have to type, the less likely you are to use it.
Since showhelp is in an operating file script, users must have to access to that file. Chances are that this would lead to distribution of copies of the script, which would make upgrades difficult. A stored PL/SQL solution would rely on the granting of execute privileges to make the utility available.
The script can only read help text from the USER_SOURCE view. Now, wait just a minute, you might find yourself protesting. Isn't that supposed to be one of the script's features? It is, but it is also a limitation. What if you want to provide help text directly from operating system files containing the original source code. A SQL -based solution would have a tough time indeed with this level of flexibility.
Let's now look at what it takes to convert this SQL*Plus script into a comprehensive packaged solution that overcomes these weaknesses.
I will start by exploring the implementation of the show procedure of PLVhlp. This procedure displays the first page of help text. A simplified version of the procedure is shown below:
PROCEDURE show (context_in IN VARCHAR2, part_in IN VARCHAR2 := c_main) IS BEGIN 1 PLVobj.savecurr; 2 PLVobj.setcurr (context_in); 3 PLVio.usrc; 4 PLVio.initsrc (help_start (part_in), help_end (part_in)); 5 PLVio.settrg (PLV.pstab); 6 PLVio.src2trg; 7 set_more (2, 0); 8 more; END;
As you can see, PLVhlp.show makes extensive use of PL/Vision packages, most importantly PLVio. This makes sense, since the help text is stored in USER_SOURCE. PLVio was designed to allow me to read PL/SQL source code from database tables and other repositories. If I could not use PLVio in PLVhlp.show , I might as well not write a book about PL/Vision.
The following sections explain each of the lines of code in the show procedure.
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.
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.
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.
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.
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.
One of the big issues I encountered in designing PLVhlp was to implement a "pause" feature. It is very difficult to fit in a single screen size all the information you want or need to present about any reasonably complex program. And yet it is also very hard for a user to watch thirty, sixty, ninety lines of text scroll rapidly by without feeling the onset of panic.
SQL*Plus handles this situation very nicely with the SET PAUSE ON and SET PAGESIZE environment commands. You simply specify the number of lines in a page and turn pause "on." Then whenever you execute a query in SQL*Plus, it automatically halts output after n lines until you press Enter. If I was using the very first implementation of online help I shared with you in the last issue, I could (and did) rely on the SQL*Plus pause feature to implement page pausing for online PL/SQL help.
I discovered, however, that while the single-query solution to online help was simple and easy, it lacked a wide variety of features I needed to implement. The solution was to move to a PL/SQL program that queried rows from the database and then displayed each row of text with a call to the builtin DBMS_OUTPUT.PUT_LINE. Could I rely on SQL*Plus's pause facility to control this output? Not a chance. For one thing, this feature was designed to work only with SELECT statement output. For another, when you start or execute a PL/SQL program from within SQL*Plus, all control is turned over to the runtime engine of PL/SQL . And no output is generated until the program finishes. So if the hlp procedure found 2000 rows of help text, it would all come spewing out uninterrupted (unless it first exceeded the size of the buffer in SQL*Plus!).
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.
There were two basic architectures I could use to display n lines of help text before terminating execution of the PLVhlp.show program:
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.
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.
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 :
1 PROCEDURE more IS 2 BEGIN 3 IF v_more 4 THEN 5 PLVio.disptrg 6 ('Help for ' || PLVobj.currname, 7 v_startrow, v_endrow); 8 9 IF v_endrow = PLVio.target_row-1 10 THEN 11 PLVobj.restore_object; 12 v_more := FALSE; 13 ELSE 14 p.l ('...more...'); 15 set_more (v_endrow + 1, v_endrow); 16 END IF; 17 ELSE 18 p.l ('No more help available...'); 19 END IF; 20 END;
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:
|
|
TRUE if there are more lines of help text to display. |
|
|
The first row in the PL/SQL table of the next page of text. |
|
|
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.
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 .
In the earlier part of the chapter, I showed you how to construct with a minimum of coding and fuss a functional utility to provide online help for PL/SQL programs. This utility consisted of a single SQL statement and it got the job (narrowly defined) done. This script did, on the other hand, have its limitations. The solution I offered in the second implementation of online help went beyond simply providing a handy tool. Instead, it took what I call a "component" approach to providing a robust environment in which PL/SQL developers can make their requests.
I take advantage of the PL/SQL package structure to allow a user of PLVhlp to modify the way help is delivered to that user. You don't simply ask to view the help text. You determine the size of a logical page of text and you control what you see when.
This shift of control to the user, this anticipation of user needs, this robustness of implementation all distinguish the powerful plug-and-play component in PL/SQL from its poor cousin, the utility. Building a component is certainly more challenging. You have to be more creative and more flexible. You have to write more code and make what you write more sophisticated. I cannot, however, understate the payoff. People (including yourself!) will use -- and reuse -- your code. They will be more productive and their programs will be noticeably less buggy and easier to maintain.
|
|
|
|
| 17.4 Using PLVhlp |
|
18. PLVcase and PLVcat: Converting and Analyzing PL/SQL Code |
Copyright (c) 2000 O'Reilly & Associates. All rights reserved.