Advanced Oracle PL/SQL Programming with Packages

15.4. Implementing PLVvu

PLVvu is a very handy utility; it is also an excellent example of the kind of software you can build yourself to get more out of the Oracle data dictionary. This section goes behind the scenes of PLVvu to help you understand how I built the package—and, perhaps as importantly, how the package evolved over time into its final PL/Vision format.

First we'll look at the general task of finding and displaying source code stored in the data dictionary. Then we'll examine the steps required to build an alternative to SHOW ERRORS.

15.4.1. How to Find Source Code

When you "create or replace" a program (procedure, function, or package) into the Oracle database, the source code is saved to the SYS.SOURCE$ table. You can view the contents of this table for all of your stored programs by accessing USER_SOURCE view. The structure of this view is:

SQL> desc user_source
 Name           Null?    Type
 -------------- -------- -------------
 NAME           NOT NULL VARCHAR2(30)
 TYPE                    VARCHAR2(12)
 LINE           NOT NULL NUMBER
 TEXT                    VARCHAR2(2000)

The Name column contains the name of the object. The name is always stored in uppercase unless you enclose the name of your program in double quotation marks at creation time. I will assume in my help implementation that you don't do this and that your program name is always uppercased. Type is a string describing the type of source code, either PROCEDURE, FUNCTION, PACKAGE, or PACKAGE BODY (always uppercase). The line is the line number and the text is the line ...