15. PLVvu: Viewing Source Code and Compile Errors

Contents:
Compiling PL/SQL Code in SQL*Plus
Displaying Compile Errors
Displaying Source Code
Implementing PLVvu

The PLVvu (PL/Vision View) package offers a set of programs which allow you to view both stored source code and compile errors. It provides an alternative to the SQL*Plus SHOW ERRORS command which offers you significantly more information about your compile problem. It allows you to quickly scan PL/SQL source code stored in the data dictionary.

This chapter shows you how to use the PLVvu programs and also explores the steps involved in constructing a utility like PLVvu. Before diving into the programs provided by PLVvu, however, let's review the situation most PL/SQL developers around the world face on a daily basis as they try to compile their code.

15.1 Compiling PL/SQL Code in SQL*Plus

Suppose that you work really, really hard at building this very complicated PL/SQL program. It's a big one, but you feel as if you've got a handle on it. You "create or replace" it in SQL*Plus and here is what you see:

SQL> start bigone.sql
Warning: Procedure created with compilation errors.

You groan. At the same time, you realize that you weren't very likely to get it all right the first time. Well, it's time to find out what the error is. Fortunately, Oracle Corporation provides a utility to view the compile errors: the SHOW ERRORS command.

SQL> show errors
LINE/COL ERROR
-------- --------------------------------------------------------------
624/10   PLS-00103: Encountered the symbol "IF" when expecting one of
         the following:
         * & = - + ; < / > in mod not rem an exponent (**)
         <> or != or ~= >= <= <> and or like between is null etc.
         ; was inserted before "IF" to continue.

Wow. Underwhelmed or what? Let's see...so, this error of some kind was found on the tenth character of line 624. Line 624, eh? I open up the file containing my fantastic new program and go down to line 624. Here is what I find:

622  FOR data_rec IN data_cur
623  LOOP
624     restructure (data_rec.key_val);
635  END LOOP;

Not an IF in sight. My sense of elation deflates. Getting this program to compile is going to be more difficult that I had thought. What is going on and why am I so depressed?

The most critical problem is that SHOW ERRORS does not actually show the line of code upon which the error was found. And even if it did show you that line, it might not necessarily reveal the error, since the error might actually occur on a different line, as you saw above.

15.1.1 Compiling Stored Code

When you compile a PL/SQL program in SQL*Plus from a file, the following actions occur:

SQL*Plus strips out all blank lines (!) and passes them on to the SQL layer ("create or replace" is a DDL statement).
The PL/SQL program is compiled. The source code in the file is loaded into the data dictionary in the SYS.SOURCE$, which has the following structure:
```
Name           Null?    Type
-------------- -------- ----
OBJ#           NOT NULL NUMBER
LINE           NOT NULL NUMBER
SOURCE                  VARCHAR2(2000)
```
When the compile is complete, the SYS.OBJECT$ table is updated with the date and time of the compile and the status (VALID or INVALID).
If there are compile errors, then that information is written to the SYS.ERROR$ table, which has the following structure:

Name           Null?    Type
-------------- -------- ----
OBJ#           NOT NULL NUMBER
SEQUENCE       NOT NULL NUMBER
LINE           NOT NULL NUMBER
POSITION       NOT NULL NUMBER
TEXTLENGTH     NOT NULL NUMBER
TEXT           NOT NULL VARCHAR2(2000)

The LINE column shows the line on which the error was found. The POSITION column contains the character offset to the token on which the error was found. Sadly, that line number reflects the "stripped" version of my program. So it doesn't correlate back to the source code in the file.

15.1.2 What SHOW ERRORS Does

The SHOW ERRORS command simply dumps the contents of SYS.ERROR$ (known, by the way, to mere mortals as the USER_ERRORS view) for the most recently compiled module. You can also display lines from USER_ERRORS for a specific program by specifying the type and name of the program, as shown:

SQL> show errors procedure greetings

This comes in handy when you have compiled (or tried to compile) multiple modules from a single script file. I am really glad that Oracle Corporation provides SHOW ERRORS, but I sure wish it were more useful. Even getting the line number on which the error occurs is not all that helpful. Sure, I can check my source code (usually in an operating system file). Yet my file line numbers will probably not match the stored code line numbers since SQL*Plus removes blank lines at compile time. I can write a query against USER_SOURCE to see my stored code, but what would be really great is if the SHOW ERRORS command at least showed the source code with which PL/SQL had its problem.

Wishful thinking does not, however, help a developer very much. I could wait until Oracle Corporation gets around to enhancing SHOW ERRORS, or maybe I could do something about it myself right now. I have learned over the years[ 1 ] two important lessons:

[1] This dates from 1991 when I built my own debugger for SQL*Forms, XRay Vision, in SQL*Forms itself.

Don't wait for Oracle Corporation to provide the finishing touches on products that improve developer productivity and general quality of life. Those enhancement requests are usually way down on the list of priorities.
I can usually build some kind of utility that goes a long way towards addressing a deficiency in the Oracle tools. It's not the same as Oracle really doing it right and it's not as polished or "shrink-wrapped" as a real third-party vendor solution, but it can still have a noticeable impact on my productivity.

The next section offers an alternative to SHOW ERRORS that handles many of the problems of this builtin command. This package should come in very handy, and it should also serve as a lesson (maybe even an inspiration) to all of my readers out there: don't whine, design! If you've got a complaint and you've got a need, take development into your own hands and build yourself a solution.