#

| Chapter 15 PLVvu: Viewing Source Code and Compile Errors |

## 15.2 Displaying Compile Errors

The `err` procedure of the PLVvu package offers a very useful alternative to the SQL*Plus SHOW ERRORS command. The header for this procedure is:

>     PROCEDURE err
>        (name_in IN VARCHAR2 := NULL, overlap_in IN INTEGER := overlap);

You provide the name of the program unit for which you want errors displayed and the `err` procedure not only displays all errors found in the USER_ERRORS view, but it also shows you exactly which lines of code are causing the problem. The second argument specifies the numbers of lines of code to display around the line with the compile error. The default value is the value set by the `set_overlap` procedure (described in a later section).

If you do not supply a program name, `PLVvu.err` will show you the compile errors for the most recently-compiled program unit. It determines this information by searching for the object in ALL_OBJECTS whose `last_ddl_time` equals the MAX (`last_ddl_time`).

The format for specifying a program unit is explained fully in Chapter 11, PLVobj: A Packaged Interface to ALL_OBJECTS. Briefly, you can supply only the name, the type:name (as in "b:PLVio" for the body of the PLVio package), or even the type:schema.name (as in "s:scott.showemps" to see the specification of the `showemps` package owned by SCOTT).

The `err` procedure tries to be smart about displaying the surrounding lines of code. Suppose, for example, that you have errors on two consecutive lines (318 and 319) and you have specified 10 lines of overlap. You would not want to see lines 309 through 318 as well as 319 through 328, twice, would you? The logic required to handle this complexity is covered in Section 15.4.2, "Implementing the SHOW ERRORS Alternative"

PL/Vision also provides a script named `sherr.sql` so that you do not have to type the full execute command for the `PLVvu.err` procedure at the SQL*Plus prompt. The following two requests to show errors are, therefore, equivalent:

>     SQL> exec PLVvu.err
>     SQL> @sherr

If you want to pass the name of a particular program to `PLVvu.err`, you will not be able to use the `sherr.sql` script.

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

15.1 Compiling PL/SQL Code in SQL*Plus |  | 15.3 Displaying Source Code

Copyright (c) 2000 O'Reilly & Associates. All rights reserved.