Advanced Oracle PL/SQL Programming with Packages

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 ...