11. PLVobj: A Packaged Interface to ALL_OBJECTS

Contents:
Why PLVobj?
ALL_OBJECTS View
Setting the Current Object
Accessing ALL_OBJECTS
Binding Objects to a Dynamic Cursor
Populating a PL/SQL Table with Object Names
A Programmatic Cursor FOR Loop
Tracing PLVobj Activity

The PLVobj (PL/Vision OBJect) package provides a programmatic interface to the PL/SQL objects stored in the ALL_OBJECTS data dictionary view. It is used throughout PL/Vision in two ways:

To parse and manage a "current object," which is composed of the schema, name, and type of the object. The PLVobj package handles the complexity of parsing various versions of the current object specification. It also uses NAME_RESOLVE to locate the object you specify in the data dictionary.
To easily fetch objects from the ALL_OBJECTS view. With the programmatic interface between you and the ALL_OBJECTS view, you never have to explicitly open, fetch from, or close a cursor against this view in order to retrieve object information. Instead, you call PL/SQL programs which do the job for you.

PLVobj offers some excellent lessons in how to use packages to:

Hide implementational and data structure details from developers who don't want or need to deal with that level of detail.
Use the persistent characteristic of packaged variables to implement a current object that can be used in many different programs and circumstances.
Provide a comprehensive procedural interface to a cursor. This includes the loopexec program, which simulates a cursor FOR loop against the cursor.

The PLVobj package is not a flashy piece of software. It isn't anything end users or even developer users will ever really see. It is, however, a very useful low-level building-block component for developers who work with this data dictionary view and who may want to build similar interfaces to other predefined views.

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.