|
Chapter 15 PLVvu: Viewing Source Code and 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.
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:
I left off the right parenthesis in my call to PLV io.put_line .
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.
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.