Advanced Oracle PL/SQL Programming with Packages

16.4. Implementing PLVgen

The PLVgen package is a large package. The body alone contains more than 20,000 characters spread over 1,390 lines of code. It is not, however, a terribly complicated package. Each of the "public" programs (the code generators) is, at heart, a sequence of commands to output various combinations of text.

The biggest challenge in constructing PLVgen was to minimize the amount of redundant code. There are many common lines of text, for example, between the template for a function and procedure. And my package allows you to generate many different kinds of functions and procedures. If I simply hard-coded the lines of text to be output for each different program unit, the PLVgen package would run into two problems:

1. It would grow to monstrous proportions and eventually (soon?) hit the upper limit for code size in PL/SQL.

2. It would be difficult to maintain and enhance. I would have to constantly cut and paste to build new generator procedures, since there would be little shared code. And what if I discovered a mistake in all of my different function generators? Or, even more likely, what if I want to add another toggle to fine-tune the look-and-feel of the generated code? I would have to make changes across many different program units in the package.

PLVgen is definitely one of those packages where a building-block approach was absolutely critical to a successful implementation. To explain PLVgen, I first review some of the best practices and coding styles I ...