Oracle PL/SQL Best Practices

Name

PKG-08: Avoid bloating package code with unnecessary but easy-to-build modules.

Synopsis

This best practice definitely applies to the author of this book. Once you get started building packages, especially packages that provide an interface to underlying functionality, it’s so, so easy to get excited about all the possibilities, all the different programs that can and should be a part of that interface. Why not add a function that calculates X, or a procedure that displays Y?

The result can be best characterized by a rephrasing of that oft-repeated riddle:

If you write a program and no one uses it, does that program really exist?

And it’s not just a matter of wasted effort. By loading up a package with programs no one necessarily wants or needs, you make it harder for anyone to find the programs that they actually do want or need. You are better off taking a minimalist approach to building your packages: build only what is needed at this point in time. Implement those requirements in the simplest, most direct manner possible (based on best practices, of course).

Example

Gosh, there are actually a number of examples to choose from in my own toolbox. All right, how about the PLVgen package of PL/Vision? This package is a handy code generator. You can, for example, spit out function templates. You can direct the output to the screen or a file. But wait! That’s not all. You can also send the generated code to a database pipe or even a “PL/SQL table” (now called an index-by table