16. PLVgen: Generating PL/SQL Programs

Contents:
Options for Best Practices
Code Generated by PLVgen
Modifying PLVgen Behavior
Implementing PLVgen

The PLVgen (PL/Vision GENerator) package provides a set of procedures you can use to generate your own PL/SQL code. With PLVgen you can:

Implement coding standards and best practices by incorporating those elements into the code generated by the package.
Improve developer productivity. You don't have to do as much typing; the code is generated for you -- and it comes free of typos.
Take full advantage of PL/Vision programming components. When you generate code with PLVgen, it includes calls to PLVhlp, PLVtrc, and PLVexc package elements. PLVgen can make practical the widespread deployment of a library like PL/Vision.

16.1 Options for Best Practices

I teach a series of classes called "Achieving PL/SQL Excellence." I spend a lot of time in those classes talking about "best practices," the guidelines and techniques you should follow to write excellent PL/SQL programs. It's not enough to simply know how to write a procedure, function, or package. You need to know how to write those modules so that you are productive and so that the code is readable, efficient, and maintainable. That is a far more challenging task.

There are a couple of different options to implementing best practices:

The manual (a.k.a. "hard") way: Write or obtain a document that lists those best practices. Make sure that all developers study this guide and then set up a code review process to make sure that the standards and techniques have been followed.
The automatic way: Build or obtain a development environment that incorporates, generates, and automatically promotes the use of your best practices.

It should be pretty obvious to everyone which of these two options is preferable. Yet it is more than a matter of preference. The manual approach is also thoroughly impossible to apply with any degree of success. It requires a level of discipline and commitment from each developer that simply isn't practical. In addition, there are no tools available that allow you to do your code review in any practical fashion.

The automatic way is undeniably the way to go -- but who's going to get you going? After years of developer agony, third-party tools vendors and Oracle Corporation itself just getting around to offer a debugger. No one is addressing seriously how to improve the code construction phase. So the issue then becomes: what can you build yourself (or get from someone else) to improve your development environment and the quality of code written in your shop?

The answer, it turns out, is that you can build an amazingly useful array of utilities and components. While these home-grown solutions are not as powerful and easy to use as the real products we will eventually be able to purchase, they can have a dramatic impact on your work now. Various chapters in this book provide examples of such components, including an alternative to SHOW ERRORS and a mechanism to deliver online help for PL/SQL programs.

In this chapter, I present a package that generates PL/SQL code that can conform to an organization's standards; it enforces best practices by making it extremely easy to follow those best practices. I built PLVgen because I got tired of doing all the typing necessary to follow my own standards. I started to feel like a robot, and when that happens I know that there must be a way to automate what I am doing. You can easily build upon this package to support your own approaches to code.

16.1.1 Generating a Best Practice

I'll give you an example of how PLVgen has improved my life. One very important best practice in package construction is to never declare variables in the package specification (see Chapter 2, Best Practices for Packages ). This means that the variable is "public" and can both be read and be modified directly by any user with execute authority on the package. Instead, you should declare variables inside the body of the package. Once you do this, however, you must provide get-and-set or "gas" routines in the specification to retrieve the value of the variable (get) and change the value of the variable (set). You must, in addition, declare the private variable and build the get-and-set routines in the package body.

Each of these programs is straightforward, but also each takes time to write -- especially if I want to get my formatting correct (use of upper- and lowercase, indentation, comments, etc.) and follow my templates for program structure. Before the advent of PLVgen, I would take the time to write this code, but would feel the minutes ticking by.

Now with the PLVgen package, I can generate all the code I need to do it right -- to hide my data and build the get-and-set code. The following command executed in SQL*Plus, for example, generates a get-and-set for a packaged variable named pagesize :

SQL> exec PLVgen.gas ('pagesize', 1, 25);

Depending on the toggles I have turned on for generated code content, the PLVgen.gas procedure could produce anywhere from 18 to 50 lines of perfectly formatted, bug-free code -- in seconds.

I am a rapid typist and I know my standards inside and out. Regardless, a call to PLVgen is far more efficient than anything I can achieve with the old-fashioned (pre-PL/Vision) approach. The advantages, however, even go beyond this efficiency. The PLVgen package makes use of another PL/Vision package, PLVio, to put a line on the screen. If you so desire, you can redirect the output of the package to a different target repository, including a PL/SQL table, a database table, and, with Release 2.3 of PL/SQL , a file. So you can plug-and-play PLVgen within a GUI interface that allows a developer to construct standards-smart and library-aware PL/SQL programs.

That should give you a feel for the advantages of generating code with PLVgen. In the next section I review the full set of program units and code fragments you can generate with PLVgen. Later, I'll present the techniques used to implement PLVgen.