11.1 Why PLVobj?

PL/Vision contains a number of utilities which analyze and manipulate the contents of data dictionary views containing PL/SQL code source text. These utilities convert the case of a PL/SQL program, analyze which external programs and package elements a program references, display stored source code, show compiler errors, etc. In each of these cases I needed to take the same or similar actions again and again:

• Accept a string from the user that specifies the program unit he wants the package to work with. Convert it to the owner-name-type information I need to use when working with the data dictionary views.

• Fetch rows from one or more data dictionary views based on the program unit specified.

I would like to be able to say that as I began writing my first source-related utility I instantly recognized the need to create a package like PLVobj. The truth is that my first read of the situation was that it was very easy to define a cursor against USER_OBJECTS and get what I needed for my package. So I just started hacking away. I built the first version of my program and got it working. And then I started on my next utility. Suddenly I was confronted with having to write the same (or very similar) kind of code again. I was troubled by the redundancy. Still, it was pretty simple stuff, so I went ahead with the duplication of code. I got that second utility to work as well. Then I sent the packages to one of my devoted beta testers. He installed them in a networked environment under a common user and told his developers to try them out.

Neither utility worked. At all. It didn't take too long to figure out why. In my own, intimate development and testing environment, everything existed in the same Oracle account. In the beta environment the utilities were installed in a single account and then shared by all. My naive reliance on the USER_OBJECTS data dictionary view doomed the utilities. I needed instead to use the ALL_OBJECTS view. This meant that I also needed to provide a schema or owner to the cursor. Suddenly I had to perform less-than-trivial enhancements to two different programs.

At this point, I came to my senses. I needed to consolidate all of this logic, all code relating to the objects data dictionary view, into a single location -- a package. I could not afford, in terms of productivity and code quality, to have code redundancy. As you begin to use new data structures or develop a new technique the first time, it is sometimes difficult to justify cleaving off the code to its own repository or package. When you get to needing it the second time, however, there should be no excuses. Avoid with fanatical determination any redundancies in your application code.

And so PLVobj was born. Of course, the version I share with you is very different from the first, second, third, and fourth versions of the package. Believe me, it has changed a lot over a four-month period. I seem to come across new complexities every week. (For example, a module name is not always in upper case; you can create program units whose names have lowercase letters if you enclose the name in double quotes.)

The PLVobj package offers functionality in several areas:

• Set and view the "current object" maintained inside the package.

• Access a cursor into the ALL_OBJECTS view, providing a full range of cursor-based functionality through PL/SQL procedures and functions.

• Trace the actions of the package.

The elements available in PLVobj are described in the following sections. Before diving into the programs, however, let's review the ALL_OBJECTS view.