PLVvu is a very handy utility; it is also an excellent example of the kind of software you can build yourself to get more out of the Oracle data dictionary. This section goes behind the scenes of PLVvu to help you understand how I built the package -- and, perhaps as importantly, how the package evolved over time into its final PL/Vision format.
First we'll look at the general task of finding and displaying source code stored in the data dictionary. Then we'll examine the steps required to build an alternative to SHOW ERRORS.
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:
SQL> desc user_source 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 uppercase unless you enclose the name of your program in double quotation marks at creation time. I will assume in my help implementation that you don't do this and that your program name is always uppercased. Type is a string describing the type of source code, either PROCEDURE, FUNCTION, PACKAGE, or PACKAGE BODY (always uppercase). 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.
You can also access another data dictionary view, ALL_SOURCE, to see the source of all programs you can access, even if you do not own those program units. It isn't hard to write a SQL statement to view the contents of this table. The following SQL script (found in showsrc.sql ) gets the basic idea across:
SELECT TO_CHAR (line) || text Line_of_code FROM user_source WHERE name=UPPER ('&1') AND line BETWEEN &2 AND &3 /
However, if you want to make it easy for developers to run such scripts in a flexible manner, you will probably want to move to a PL/SQL -based solution, as I did.
PROCEDURE code (name_in IN VARCHAR2 := NULL, start_in IN INTEGER := 1, end_in IN INTEGER := NULL, header_in IN VARCHAR2 := 'Code for') IS line_rec PLVio.line_type; line_num INTEGER; BEGIN set_object (name_in); PLVio.asrc (start_in, end_in); disp_header (header_in); LOOP PLVio.get_line (line_rec, line_num); EXIT WHEN line_rec.eof; disp_text (line_rec.line# + start_in - 1, line_rec.text); END LOOP; PLVio.closesrc; END;
As you can see, PLVvu relies heavily on other PL/Vision packages. It calls a private procedure, set_object , to set the current object for PLVio. The set_object procedure in turn calls the PLVobj.setcurr program for either the name specified or the last object. The code procedure then calls PLVio.asrc to set the source repository to the ALL_SOURCE view and specifies that only lines between start_in and end_in be queried. It displays a header and then loops through each record retrieved with a call to the PLVio.get_line procedure. It calls a local procedure, disp_text , to format and display the code. When done, it closes the source repository with a call to the PLVio.closesrc procedure.
This code is certainly more complex than the single query of the showsrc.sql script. It is, on the other hand, much more powerful and flexible. For example, this approach makes it quite feasible to enhance the PLVvu.code procedure to read the source code from an operating system file (remove the call to PLVio.asrc and let the user establish her own source repository outside of PLVvu.code ). Now how would you do that with a straight SQL solution?
I could start off by showing you the source code for PLVvu.err and simply step you through this final, polished version of the program. That approach would, however, be both misleading and intimidating. The programs that you see in these pages have been massaged quite thoroughly over a period of months. If I simply explain my final version without giving you a sense of the process by which I arrived at this pristine result, you will be much less likely to develop the skills needed to come up with solutions to your own, very specific problems.[ 2 ] As a result, I will make every effort to show you the techniques of problem-solving and iterative coding I have employed en route to producing this package.
One of the critical early steps in designing a new package or new kind of utility is to validate the feasibility of the idea. In the case of PLVvu.err , my core idea is:
Let's see if I can actually do this. First, I need the SQL statement that will retrieve the source code from the USER_SOURCE view. The following SQL*Plus script displays all the code associated with the specified program unit ( &1 and &2 are parameters, allowing values to be passed into the script).
COLUMN line FORMAT 99999 COLUMN text FORMAT A80 SELECT line, text FROM user_source WHERE name = UPPER ('&2') AND type = UPPER ('&1') ORDER BY line;
I can call this script (I'll name it showcode.sql ) in SQL*Plus as follows:
SQL> start showcode procedure greetings LINE TEXT ------ ---------------------------------------------------------------- 1 procedure greetings 2 is 3 begin 4 dbms_output.put_line ('hello world!') 5 end;
Do you notice anything amiss in the above procedure? There is no semicolon at the end of line 4! In fact when I tried to "create or replace" this procedure, I was informed that:
Warning: Procedure created with compilation errors.
and my good friend SHOW ERRORS revealed the following:
LINE/COL ERROR -------- -------------------------------------------------------------- 5/1 PLS-00103: Encountered the symbol "END" when expecting one of the following: := . ( % ; Resuming parse at line 5, column 5.
If SHOW ERRORS can show the information, that means that there is a row in the USER_ERRORS view. Let's use SQL similar to the code in showcode.sql to show the error information stored in the view. The following script ( showerr.sql ) succeeds in producing output which matches SHOW ERRORS:
COLUMN linedesc FORMAT A8 HEADING 'LINE/COL' COLUMN text FORMAT A62 HEADING 'ERROR' SELECT TO_CHAR (line) || '/' || TO_CHAR(position) linedesc, text FROM user_errors WHERE name = UPPER ('&2') AND type = UPPER ('&1') ORDER BY line;
So these two SQL statements separately give me what I need -- but I need to combine them. The big question is now: can I merge these two SQL statements successfully? To merge data in SQL , I perform a join. The following SELECT will, therefore, return a line of text from USER_SOURCE for every row in the USER_ERRORS view.
SELECT TO_CHAR (S.line) || '/' || TO_CHAR(E.position) linedesc, S.text FROM user_errors E, user_source S WHERE E.name = UPPER ('&2') AND E.type = UPPER ('&1') AND E.name = S.name AND E.type = S.type AND E.line = S.line ORDER BY S.line;
When I run this script as showmerg.sql in SQL*Plus, I see the following:
SQL> start showmerg procedure greetings LINE/COL CODE -------- -------------------------------------------------------------- 5/1 end;
So now I can display just those lines of code for which there are errors -- but what is the error? I seem to have lost that part of the equation. I haven't really merged my information yet; I have only used the USER_ERRORS view to "filter" out all those lines for which there are no errors. Furthermore, it is worth pointing out right now that even if I displayed the error along with the line displayed above, I wouldn't be giving myself a whole lot with which to work. Line 5, after all, is not really in error. That's just where the compiler got lost. So if I actually want to get something out of my substitute for SHOW ERRORS, I need to display at least a couple of lines around where the error occurred.
There are two different approaches I can use to accomplish this goal: enhance the SQL or switch to a PL/SQL -based implementation. My most natural inclination is to move on to PL/SQL when the SQL going gets the least bit tough. Others, of course, may stick it out longer in the SQL layer. For example, it would be a relatively simple matter to replace the following WHERE clause of the showmerg.sql script:
AND E.line = S.line
with this BETWEEN operator:
AND S.line BETWEEN E.line-2 AND E.line+2
Then the SQL statement would display up to two lines on either side of the error-infested line of code, as shown below:
LINE/COL CODE -------- -------------------------------------------------------------- 4/1 begin 5/1 dbms_output.put_line ('hello world!') 6/1 end;
I could even get really fancy (for me, anyway) and use DECODE to slip the error text in along with the source listing:
SELECT TO_CHAR (S.line) || '/' || TO_CHAR(E.position) linedesc, DECODE (S.line, E.line, S.text|| LPAD ('*', E.position) || CHR(10) || E.text, S.text) text FROM user_errors E, user_source S WHERE E.name = UPPER ('&2') AND E.type = UPPER ('&1') AND E.name = S.name AND E.type = S.type AND S.line BETWEEN E.line-2 AND E.line+2 ORDER BY E.line;
Obligatory DECODE translation: If the source line number equals the error line number, then display the source followed immediately by a line with an asterisk under the position identified by the compiler as containing the error, followed by the error text. Otherwise just display the source text. With the above script (named merge2.sql ), I generate this output for the greetings compile error:
SQL> start merge2 procedure greetings LINE/COL CODE -------- -------------------------------------------------------------- 4/1 begin 5/1 dbms_output.put_line ('hello world!') 6/1 end; * PLS-00103: Encountered the symbol "END" when expecting one of the following: := . ( % ; ; was inserted before "END" to continue.
Pretty neat, huh? SQL , especially the version provided by Oracle Corporation can be very entertaining and effective. So why, you must be asking, should we bother with a PL/SQL -based implementation? Looks like this does everything we want...or does it?
So far we have only tested our script with a very basic procedure and error scenario. Let's throw a couple of monkey wrenches at the merge2 script and see what we get. For starters, let's simply change the mistake in the greetings procedure. Rather than leaving off the semicolon, I will remove the underscore from the builtin name put_line :
create or replace procedure greetings is begin dbms_output.putline ('hello world!'); end; /
The output from merge2 then becomes:
LINE/COL CODE -------- -------------------------------------------------------------- 2/16 is 2/4 is 3/16 begin 3/4 begin 4/16 dbms_output.putline ('hello world!'); * PLS-00302: component 'PUTLINE' must be declared 4/4 dbms_output.putline ('hello world!'); * PL/SQL: Statement ignored 5/16 end; 5/4 end;
Lots of redundant lines of code! The reason for this extra output is that the compiler has actually generated two compile errors for the same line (one at position 4 and one at position 16).
4/4 PL/SQL: Statement ignored 4/16 PLS-00302: component 'PUTLINE' must be declared
And it just gets worse and worse from there. For example, if I add an OPEN statement to my greetings procedure and reference an undefined cursor, I will get four error messages from SHOW ERRORS:
4/4 PL/SQL: Statement ignored 4/16 PLS-00302: component 'PUTLINE' must be declared 5/2 PL/SQL: SQL Statement ignored 5/7 PLS-00201: identifier 'BLOB' must be declared
The output from a call to merge2 , however, totals 18 lines (!) featuring both redundant, consecutive lines and extra "overlap" lines (the two lines preceding the second error include both the earlier error and its preceding line, so we get to see way too much and in the wrong order).
At moments like these, I thank my lucky stars for PL/SQL . There are clearly way too many exception conditions and special cases to stay within the non-procedural SQL environment. I need some old-fashioned explicit cursors with loops and IF statements. I need a procedural language.
With PL/SQL , I can relax. I am no longer constrained to come up with a creative/ingenious/obscure way to pack all my logic into a single, set-at-a-time SQL statement. I can employ traditional top-down design techniques to think through each layer of complexity. I will approach a PL/SQL solution in two stages: a direct evolutionary process from the SQL to a PL/SQL procedure (called showerr ) and then a full-blown, PL/Vision-based implementation ( PLVvu.err ).
Here is the basic algorithm of my showerr procedure:
In top-down PL/SQL code we have:
FOR err_rec IN err_cur LOOP FOR line_ind IN err_rec.line-2 .. err_rec.line+2 LOOP disp_line(line_ind); IF line_ind = err_rec.line THEN /* Point to error, show message. */ DBMS_OUTPUT.PUT_LINE (LPAD ('*', err_rec.position+8)); DBMS_OUTPUT.PUT_LINE (RPAD ('ERR', 8) || err_rec.text); END IF; END LOOP; END LOOP;
where the error cursor looks like this:
CURSOR err_cur IS SELECT line, position, text FROM user_errors WHERE name = UPPER (name_in) AND type = UPPER (type_in) ORDER BY line;
PROCEDURE disp_line (line_in IN INTEGER) IS CURSOR src_cur IS SELECT S.line, S.text FROM user_source S WHERE S.name = name_in AND S.type = type_in AND S.line = line_in; src_rec src_cur%ROWTYPE; BEGIN OPEN src_cur; FETCH src_cur INTO src_rec; DBMS_OUTPUT.PUT_LINE (RPAD (TO_CHAR (line_in), 4) || src_rec.text); CLOSE src_cur; END;
This rapid, top-down driven design process yields a working program in short order. When I run this initial version, however, I find a number of complications (try it yourself; the code is stored in showerr1.sp ):
The reason I detail all of these issues for you is to emphasize a truth that I learn over and over again (unfortunately) as I develop my software:
The conclusions you should draw from this process are:
In the meantime, though, I have a showerr procedure that simply doesn't make the grade. Let's see what can be done to this standalone procedure to at least make it more useful than the original SQL solution.
We will now enhance the showerr procedure found in showerr1.sp . The result is stored in the showerr2.sp file in the use subdirectory. To my mind, a sensible requirement for showerr would be that it never display a line of code more than once. If more than one error occurs on a line, the line is displayed once and multiple error messages are placed below it. If errors occur on consecutive lines, then the overlap of code around those lines should include those lines.
The easiest way to detect whether a line has already been displayed is to keep track of the last line number displayed. In the following version of the procedure's loops, the last_line variable is initialized to 0 and then set to the current line number after it is displayed. A line is now only displayed if it is greater than the last line number.
FOR err_rec IN err_cur LOOP FOR line_ind IN err_rec.line-2 .. err_rec.line+2 LOOP IF last_line < line_ind THEN /* || Display the source & error... || This must be changed too! */ END IF; last_line := GREATEST (last_line, line_ind); END LOOP; END LOOP;
As noted in the comment above, the logic required to display the source and error must now also be adjusted. If I am going to display a line of source code only once, then I have to take special care to make sure that all error messages are displayed. In the first version of showerr , I displayed the error when the inner FOR loop's line index equaled the outer cursor FOR loop error record's line number:
IF line_ind = err_rec.line THEN ... END IF;
This same test will no longer work. Suppose I have an error on lines 4 and 6. While the outer loop is still working with the error on line 4, the inner loop will have displayed line 6 and set the last_line to 6. When the outer loop moves on to line 6, the inner loop will not display this line a second time. So when and how will I display the error information for line 6?
I will need to discard the simplistic check for a match on error line number and current source line number. Instead, whenever I have not yet displayed a line, I will call the display_line procedure to show it and then also call a new local module, display_err , to display the error information if that line does indeed have an associated row in the USER_ERRORS view:
FOR err_rec IN err_cur LOOP FOR line_ind IN err_rec.line-2 .. err_rec.line+2 LOOP IF last_line < line_ind THEN display_line (line_ind); display_err (line_ind); END IF; last_line := GREATEST (last_line, line_ind); END LOOP; END LOOP;
The display_err program is virtually identical to display_line , except that it fetches from USER_ERRORS, not USER_SOURCE, and shows the error position as well as the error information. This procedure is shown below:
PROCEDURE display_err (line_in IN INTEGER) IS CURSOR err_cur IS SELECT line, position, text FROM user_errors WHERE name = UPPER (name_in) AND type = UPPER (type_in) AND line = line_in; err_rec err_cur%ROWTYPE; BEGIN OPEN err_cur; FETCH err_cur INTO err_rec; IF err_cur%FOUND THEN DBMS_OUTPUT.PUT_LINE (LPAD ('*', err_rec.position+8)); DBMS_OUTPUT.PUT_LINE (RTRIM (RPAD ('ERR', 8) || err_rec.text, CHR(10))); END IF; CLOSE err_cur; END;
If the supplied line does not have an error, then the err_cur%FOUND attribute returns FALSE and nothing is displayed.
To understand more clearly the way that output is controlled in showerr2.sp , consider this scenario: my ten-line program has compile errors on line 4 and then again on line 6. Table 15.1 shows the progression of the counters and their impact on the display of information.
No line is ever displayed twice but there still is a problem with the showerr loop: it will sometimes display one or more blank lines with "phony" line numbers. Suppose that a program has five lines of code and there is an error on line 4. The inner FOR loop will execute for line numbers 2 through 6. There isn't any line 7, so the display_line program will not fetch any records. It will, however, still go ahead and call DBMS_OUTPUT.PUT_LINE (see source above for the display_line procedure). This hole can be plugged as follows:
OPEN src_cur; FETCH src_cur INTO src_rec; IF src_cur%FOUND THEN /* display the line */ END IF; CLOSE src_cur;
Now showerr only displays a line once and then only if it is actually in the USER_SOURCE view. In addition, it displays the USER_ERRORS information for each line with an error, even if it is first displayed as the surrounding code for an earlier error.
I identified earlier a number of ways in which the output from showerr was deficient. We've handled some of the most critical deficiencies. Let's take up some of the more cosmetic adjustments:
You just can't get away with padding a string with spaces on the left and displaying that indentation. The spaces will be stripped out by the time the user sees the output. How do you get around this problem? Prefix your string with some non-blank characters, then stick in the requisite number of spaces to move your string to the right position.
The p package of PL/Vision has, in fact, a builtin prefix feature. But for showerr and our currently non-PL/Vision based implementation, we will use a prefix which makes the error stand out from the lines of source code and has a meaning specific to this program.
My approach is to use ERR as a prefix, as shown below:
4 dbms_output.putline ('hello world!'); ERR * ERR PLS-00302: component 'PUTLINE' must be declared
It's not hard to accomplish this. I simply change these calls to PUT_LINE:
DBMS_OUTPUT.PUT_LINE (LPAD ('*', err_rec.position+8)); DBMS_OUTPUT.PUT_LINE (LPAD (err_rec.text, 8));
to the following calls:
DBMS_OUTPUT.PUT_LINE ('ERR' || LPAD ('*', err_rec.position+5)); DBMS_OUTPUT.PUT_LINE (LPAD ('ERR', 8) || err_rec.text);
Now let's get rid of those blank lines. By default, when you display a line of code from USER_SOURCE, you will end up with a blank line after the code. This occurs because there actually is a newline character (CHR(10)) at the end of each line. So if you want to end up with a display of error which is actually readable, you will need to get rid of those trailing newlines. The best way to do this is with RTRIM, as shown below for the two different calls to PUT_LINE for code:
DBMS_OUTPUT.PUT_LINE (RTRIM (RPAD (TO_CHAR (line_in), 8) || src_rec.text, CHR (10))); DBMS_OUTPUT.PUT_LINE (RTRIM (RPAD ('ERR', 8) || err_rec.text, CHR(10)));
220.127.116.11 Consolidating redundant code
Notice all the redundancies between these two calls? They cry out to be modularized into a single procedure:
PROCEDURE err_put_line (prefix_in IN VARCHAR2, text_in IN VARCHAR2) IS BEGIN DBMS_OUTPUT.PUT_LINE (RTRIM (RPAD (prefix_in, 8) || text_in, CHR(10))); END;
With this procedure, the two calls to DBMS_OUTPUT.PUT_LINE become:
err_put_line (TO_CHAR (line_in), src_rec.txt); err_put_line ('ERR', err_rec.text);
By taking this approach, I hide the specific implementation required to get my output correct (right-padding to length of eight, right-trimming the newline characters). This guarantees that the format of both these lines will be consistent with each other. And if I need to add extra lines as I enhance the procedure, I can simply call err_put_line and not have to worry about remembering all the details.
The only problem I identified in showerr1.sp which is not now corrected in showerr2.sp has to do with the way that long error messages are wrapped and then pushed to the left margin. The ideal solution would take the long error message, strip out newline characters and then rewrap the text at the specified line length. The techniques and code required to do this are described in Chapter 11 of Oracle PL/SQL Programming , in Character Function Examples . I have also incorporated string-wrapping into the PLVprs package (PL/Vision PaRSe), as you will see in my presentation below of the PL/Vision version of showerr , namely Plvvu.err .
Even without word wrap, the showerr procedure now does a pretty fair job of enhancing SHOW ERRORS, as shown below:
SQL> exec showerr ('procedure','greetings'); 2 is 3 begin 4 dbms_output.putline ('hello world!'); ERR * ERR PLS-00302: component 'PUTLINE' must be declared 5 open blob; ERR * ERR PLS-00201: identifier 'BLOB' must be declared 6 end;
Example 15.1 shows the full implementation of showerr2.sp 's version of the showerr procedure. Notice that showerr does not in any way depend on or make use of PL/Vision packages.
CREATE OR REPLACE PROCEDURE showerr (type_in IN VARCHAR2, name_in IN VARCHAR2) IS last_line INTEGER := 0; CURSOR err_cur IS SELECT line, text FROM user_errors WHERE name = UPPER (name_in) AND type = UPPER (type_in) ORDER BY line; /* Local Modules */ PROCEDURE err_put_line (prefix_in IN VARCHAR2, text_in IN VARCHAR2) IS BEGIN DBMS_OUTPUT.PUT_LINE (RTRIM (RPAD (prefix_in, 8) || text_in, CHR(10))); END; PROCEDURE display_line (line_in IN INTEGER) IS CURSOR src_cur IS SELECT S.line, S.text FROM user_source S WHERE S.name = UPPER (name_in) AND S.type = UPPER (type_in) AND S.line = line_in; src_rec src_cur%ROWTYPE; BEGIN OPEN src_cur; FETCH src_cur INTO src_rec; IF src_cur%FOUND THEN err_put_line (TO_CHAR (line_in), src_rec.text); END IF; CLOSE src_cur; END; PROCEDURE display_err (line_in IN INTEGER) IS CURSOR err_cur IS SELECT line, position, text FROM user_errors WHERE name = UPPER (name_in) AND type = UPPER (type_in) AND line = line_in; err_rec err_cur%ROWTYPE; BEGIN OPEN err_cur; FETCH err_cur INTO err_rec; IF err_cur%FOUND THEN DBMS_OUTPUT.PUT_LINE ('ERR' || LPAD ('*', err_rec.position+5)); err_put_line ('ERR', err_rec.text); END IF; CLOSE err_cur; END; BEGIN /* Main body of procedure. Loop through all error lines. */ FOR err_rec IN err_cur LOOP /* Show the surrounding code. */ FOR line_ind IN err_rec.line-2 .. err_rec.line+2 LOOP IF last_line < line_ind THEN display_line (line_ind); display_err (line_ind); END IF; last_line := GREATEST (last_line, line_ind); END LOOP; END LOOP; END; /
We now have in hand a working prototype of a program to display more useful compile error information. It is a relatively sophisticated piece of code; it has three local modules, manipulates the contents of the data dictionary, and compensates for several idiosyncrasies of DBMS_OUTPUT in SQL*Plus. There is still a big difference, though, between a program which validates "proof of concept" and a polished utility that handles all circumstances gracefully.
I will list a few of the ideas I have uncovered to improve upon showerr . Then I will show you how I implemented some of these improvements with the PLVvu package (PL/Vision VU), which builds upon many other PL/Vision packages.
Here are ways I could improve the functionality and usability of showerr :
PLVvu.err achieves improvements in ease of use and output appearance through two means:
There isn't anything really complicated in the body of PLVvu.err , because so much of it has been modularized -- either elsewhere within the PLVvu package or into other, prebuilt packages like PLVobj and PLVio.
As you build increasing numbers of generalized, reusable packages, you will find that the development and debugging time required for new programs decreases dramatically. Even though I do not explain in this chapter the complete implementations of my various PL/Vision packages, you should be able to see how they "plug-and-play". You can do the same thing in your own environment, to meet your own application-specific requirements.
Copyright (c) 2000 O'Reilly & Associates. All rights reserved.