Oracle PL/SQL Best Practices

Name

PKG-11: Separate package specifications and bodies into different source code files.

Synopsis

Don’t combine the specification and body of a package in the same file. Instead, store them in their own files and then, in your installation script for your product, compile all specifications first, followed by the package bodies. By taking this approach, you will find it easier to install and maintain your code base.

Over time, it’s likely that your package specification will stabilize, and most changes will take place in the package body. All references to elements in a package are resolved with the specification. If the specification is recompiled, all dependent objects are marked INVALID and must be recompiled. By putting the body in its own file, you can change and recompile it without affecting the status of any other programs.

Example

See the various .pks and corresponding .pkb files provided on the Oracle PL/SQL Best Practices web site.

You will also find a number of .pkg files on the site. I admit that these files violate this best practice. I decided to take this approach because they are small, self-contained packages, designed to be deployed easily in your own application environment.

PL/Vision is an example of a much more complex base of code. For this library, all package specifications and bodies are stored in their own files.

Benefits

You can maintain and recompile packages without causing a “domino effect” that invalidates many other (unchanged) programs.

Your code will ...