Advanced Oracle PL/SQL Programming with Packages

2.9. Modularizing for Maintainable Packages

To build packages that are both immediately useful and enhanceable over the long-run, you must avoid any kind of code duplication inside the package. You need to be ready, willing, and able to create private programs in your package to contain all the shared code behind the public programs of the package. The alternative is a debilitating reliance on the Windows cut-and-paste feature. Cut-and-paste will let you build rapidly—but what you will be building is a wide, deep hole from which you will never see the light of day.

I set a simple rule for myself when building packages: never repeat a line of code. Instead, construct a private module and call that module twice (or more, depending on the circumstances). By consolidating any reused logic rigorously, you have less code to debug and maintain. You will often end up with multiple layers of code right inside a single package. These layers will make it easier to enhance the package and also to build in additional functionality, such as the windows and toggles discussed earlier in this chapter.

The PLVdyn package offers an example of in-package layering. As explained in the section on "Building Windows into Packages," the open_and_parse function consolidates the open and parse phases of dynamic SQL. This function is then called by many other higher-level operators in PLVdyn. These operators are in turn called by still other programs. The result is at least five layers of code as shown ...