15.2.1 Impact of PLVvu.err

Consider the SQL*Plus session shown below. First, we have the output from SHOW ERRORS. It reveals that compile errors were found on lines 333 and 349. As usual, it is very difficult to determine from these error messages what is actually wrong with my program and how I should go about finding the source of the problems. It is hard to even tell what the problem is because SHOW ERRORS does not display the line of code in which the error was found.

SQL> show errors
Errors for PACKAGE BODY PLVGEN:
LINE/COL ERROR
-------- --------------------------------------------------------------
333/53   PLS-00103: Encountered the symbol ";" when expecting one of
         the following:
         . ( ) , * @ % & | = - + < / > in mod not rem => ..
         an exponent (**) <> or != or ~= >= <= <> and or like etc.
         ) was inserted before ";" to continue.
349/4    PLS-00103: Encountered the symbol "BEGIN" when expecting one
         of the following:
         begin end function package pragma procedure form
         Replacing "BEGIN" with "begin".

Now let's check out the PLVvu alternative. In the following SQL*Plus session, instead of typing SHOW ERRORS, I call the PLVvu.err procedure. Since I do not provide a program name, it automatically locates the last compiled object, finds some errors, and displays the information.

SQL> exec PLVvu.err
-----------------------------------------------------------------------
PL/Vision Error Listing for PACKAGE BODY PLVGEN
-----------------------------------------------------------------------
Line#  Source
-----------------------------------------------------------------------
  331          put_line;
  332       END IF;
  333       PLVio.put_line (indent_stg (plus_in) || stg_in;
ERR                                                        *
    PLS-00103: Encountered the symbol ";" when expecting one of
    the following:  . ( ) , * @ % & | = - + < / > in mod not rem
     => ..     an exponent (**) <> or != or ~= >= <= <> and or
    like etc.  ) was inserted before ";" to continue.
  334       IF blanks_in IN (c_both, c_after)
  335       THEN
-----------------------------------------------------------------------
  347        plus_in IN INTEGER := 0,
  348        blanks_in IN VARCHAR2 := c_none);
  349    IS
ERR       *
    PLS-00103: Encountered the symbol "BEGIN" when expecting one
    of the following:  begin end function package pragma
    procedure form Replacing "BEGIN" with "begin".
  350    BEGIN
  351       IF using_cmnt
-----------------------------------------------------------------------

Well, that certainly looks different! The same two error messages show up (immediately after the word ERR on the left margin of the output). But in addition to those error messages, the PLVvu.err procedure displays the line of source code with which PL/SQL had a problem, as well as two lines before and after that problematic line of code.

With this added data, it is very easy for me to see what went wrong:

1. I left off the right parenthesis in my call to PLV io.put_line .

2. I included a semicolon at the end of line 348 -- this usually happens when I copy the header for a program out of the package specification and then paste it into the body and forget to remove the semicolon.

Notice that in both these cases the lines of code that were in error were not the lines of code indicated by the compiler. Hey, no compiler's perfect, right? In any case, I am able to immediately return to my source code and make the corrections. I have found that the PLVvu.err procedure can save me a solid 30 seconds each time I run into a compile error -- and believe me, I run into lots of compile errors with my code. This utility alone saves me an incredible amount of development time.

15.2.2 Setting the Code Overlap

You can specify the number of lines to display around the line in error by using the set_overlap procedure:

PROCEDURE set_overlap (size_in IN INTEGER := c_overlap);

The default value for the single size argument is provided by the package constant, c_overlap , which has a value of 5. So if you never call set_overlap , PLVvu will display five lines of code before and after a line with a compile error.

The following line of code changes the overlap to only two lines:

PLVvu.set_overlap (2);

And this call to set_overlap changes the overlap back to the default, since no value is provided.

PLVvu.set_overlap;

You can obtain the current value of the overlap by calling the overlap function:

FUNCTION overlap RETURN INTEGER;

If you don't like the default value of five lines, you might include a call to PLVvu.set_overlap in your login.sql to make sure that it is always set the way you like it in SQL*Plus.