Advanced Oracle PL/SQL Programming with Packages

18.2. PLVcat: Cataloguing PL/SQL Source Code

The PLVcat package gives you a way to parse and store (in a table-based catalogue) information about the contents of PL/SQL package specifications. You will glean information from this catalogue that is currently unavailable from the Oracle Server data dictionary.

Before describing the different areas of functionality of PLVcat, let's look at the problem PLVcat is intended to solve.

18.2.1. Knowing What You've Got

One of the tremendous advantages of storing PL/SQL programs in the database is that they can be executed by anyone granted authority to those objects. You don't have to know where the source code resides on disk; link lists and paths will not badger you in the world of PL/SQL. Yet you do face other challenges when seeking to leverage stored code—especially on an enterprise-wide basis. These obstacles include:

• Knowing what is available. How do you know what programs are stored where, what they are supposed to do, and how you are supposed to use them?

• Knowing where and how programs are being used. How do you measure the impact of changing a particular program? It is often very useful to be able to answer questions like which programs use this function? How, then, is the function used?

At the time I'm writing this book, I think that it is fair to say that the potential and functionality of PL/SQL have outstripped the features of development environments using PL/SQL. Developers using PL/SQL often work within a light fog, stumbling ...