15.4.2.1 Merging source and error information

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:

The USER_SOURCE view contains the source code, including line number. The USER_ERRORS view contains the compile error information, which includes line numbers as well. So it seems as if I should be able to combine these two sources of information into a single useful presentation like the one you saw earlier.

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;

15.4.2.2 Showing the line in error

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.

15.4.2.3 Going beyond SHOW ERRORS

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?

15.4.2.4 Pushing the limits of an SQL solution

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.

15.4.2.5 Moving to a PL/SQL-based solution

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:

A cursor FOR loop retrieves each of the rows from USER_ERRORS for a given program unit. For each error line retrieved, another cursor fetches lines of source code from USER_SOURCE which surround the line in error. DBMS_OUTPUT.PUT_LINE is then used to send all of these lines to the screen (or DBMS_OUTPUT buffer).

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;

The disp_line procedure displays the source for the specified line number. It simply fetches the row from USER_SOURCE and displays it with indentation using PUT_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;

15.4.2.6 The devil in the details

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 ):

1. When code is stored in the database, a newline character is placed on the end of each line. So when I use DBMS_OUTPUT.PUT_LINE to display the line, I end up with "double-spacing". SQL*Plus automatically strips off those newlines and showerr would have to do the same thing.

2. This program still shows multiple instances of the same lines of code, because I apply my "surround the error" algorithm for each error, even when these errors apply to the same line or consecutive lines.

3. The program automatically indents the source and error text to the eighth column. This works for the first line of a long error message, but all other lines (broken up by embedded newline characters) are justified flush with the left margin. It just doesn't look very professional.

4. The showerr procedure uses LPAD to put just the right number of spaces (based on the position column value) in front of an asterisk so as to point to the location in the line where the error was found. Yet when the program runs, the * always ends up on the first column. It turns out that when you display output with PUT_LINE in SQL*Plus, all leading spaces are trimmed. The LPAD is rendered useless.

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:

• Design your software to be as flexible and extensible as possible.

• Use top-down design to ensure that you call modules rather than execute an endless, complex series of statements.

• Use packages from the get-go, because you are going to find that your standalone module really does belong with other programs in a package.

• When you build functions, think about different ways that they might be used, and design that wider scope into the initial implementation (taking care to avoid gratuitous scope-creep).

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.

15.4.2.7 Showing code just once

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?

15.4.2.8 Fine-tuning code display

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.

15.4.2.9 Tracing showerr execution

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.

15.4.2.10 Cleaning up the output

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:

1. Properly position the asterisk for error position.

2. Get rid of blank lines.

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)));

15.4.2.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.

15.4.2.12 From prototype to finished product

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 :

1. Reduce to an absolute minimum the typing required in order to get error information. So far it has been necessary to type all of this:

   SQL> exec showerr('procedure','greetings');

   to obtain the needed feedback. This is lots more typing than SHO ERR (the minimum required by SQL*Plus). If developers are really going to use my alternative, I know that it has to be as easy and painless to use as possible.

   With the PLVvu package, I can replace the above verbose request to "show errors of last compile" with the following:

   SQL> exec PLVvu.err

2. Improve the flexibility of showerr . For example, I have hard-coded at 2 the number of lines of code by which the error is surrounded. Why not let the developer specify the number of lines desired (setting the default at 2)? The PLVvu package provides this flexibility as the following example shows (the request results in five lines of code before and after the line with the error):

   SQL> exec PLVvu.set_overlap (5);

3. Spruce up the error display. Two examples implemented by PLVvu are: (a) include a header explaining what is being shown and (b) place a border in the error listing when lines of code have been skipped. Both of these techniques are illustrated below:

   ----------------------------------------------------------------------- PL/Vision Error Listing for PROCEDURE TEMPPROC ----------------------------------------------------------------------- Line#  Source -----------------------------------------------------------------------    11    PROCEDURE display_line (line_in IN INTEGER)    12    IS    13       CURSOR src_cur ERR                 *     PLS-00341: declaration of cursor 'SRC_CUR' is incomplete or     malformed    14       IS    15         SELECT S.line, S.text -----------------------------------------------------------------------    32  end;    33 BEGIN    34    FOR err_rec IN err_cur1 ERR                      *     PLS-00201: identifier 'ERR_CUR1' must be declared    35    LOOP    36       FOR line_ind IN err_rec.line-fff .. err_rec.line+2 -----------------------------------------------------------------------

PLVvu.err achieves improvements in ease of use and output appearance through two means:

1. Tweaking the logic used in showerr to handle certain conditions more gracefully. Compare the source code in plvvu.spb (in the install directory) with that of showerr2.sp (in the use directory) for examples of these differences.

2. Leveraging existing PL/Vision packages to both consolidate code and improve functionality. PLVobj is used to obtain the name of the last object compiled. PLVio and PLVobj are used to read the text from USER_SOURCE. PLVprs adds paragraph-wrapping capability to the display of long error messages.

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.