Advanced Oracle PL/SQL Programming with Packages

Advanced Oracle PL/SQL Programming with PackagesSearch this book
Previous: 10.3 PLVprsps: Parsing PL/SQL StringsChapter 11Next: 11.2 ALL_OBJECTS View

11. PLVobj: A Packaged Interface to ALL_OBJECTS

Why PLVobj?
Setting the Current Object
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:

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

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:

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:

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

Previous: 10.3 PLVprsps: Parsing PL/SQL StringsAdvanced Oracle PL/SQL Programming with PackagesNext: 11.2 ALL_OBJECTS View
10.3 PLVprsps: Parsing PL/SQL StringsBook Index11.2 ALL_OBJECTS View

The Oracle Library Navigation

Copyright (c) 2000 O'Reilly & Associates. All rights reserved.

Library Home Oracle PL/SQL Programming, 2nd. Ed. Guide to Oracle 8i Features Oracle Built-in Packages Advanced PL/SQL Programming with Packages Oracle Web Applications Oracle PL/SQL Language Pocket Reference Oracle PL/SQL Built-ins Pocket Reference