Most Oracle shops still rely on SQL*Plus to create and compile PL/SQL programs. This means that the source code resides in one or more operating system files. To avoid losing control of that source, you should adopt some simple conventions for the extensions of your files. The approach I have taken is shown in the table below.
With your code separated and easily identified in this manner, you will be able to locate and maintain it more easily. You can fine-tune these extensions even more. For example, I often use the ".tab" extension for SQL*Plus Data Definition Language (DDL) scripts that create tables. The most important aspect of these naming conventions is the implied separation of package specification and body ( sps and spb ).
There are two advantages to creating and compiling specifications and bodies separately:
Codependency is not just an issue for psychologists and the self-help publishing industry. It can also rear its ugly head with PL/SQL packages. Suppose that package A calls a program in package B, and that package B calls a program in package A. These two packages depend on each other. How can one be defined before the other? How can either or both of these packages be made to compile? Simple: define all specifications and then define all bodies.
I ran into this codependency problem just before I was to give a class on packages. I planned to give out a copy of PL/Vision Lite and started work on an installation disk. Most of my packages were stored in spp files. The package specification and body were, in other words, stored in the same file. So I placed calls to execute all of these scripts in my installation file and tested the process in a fresh (of PL/Vision) Oracle account. The installation failed miserably and I couldn't understand the problem. I was able to compile any of these individual packages in my existing PL/Vision account (PLV) without any difficulty.
Suddenly, I realized the problem: when I compiled a package in my PLV account, it could reference the other packages that already existed. The package would, as a result, compile successfully. In an account with no preexisting PL/Vision code, however, when I tried to compile the p package body (a very basic package used by almost every other package in PL/Vision), it could not find the PLVprs package, which was not yet defined because it referenced the p package (among others). PLVprs was compiled later in the installation script.
For about five minutes I despaired. Had I constructed a product that wasn't even capable of installing? Then I came to my senses. The package specification and body do not have to be compiled together. And if the p package relies on the PLVprs package, it only requires the package specification for PLVprs to be in place. The PL/SQL compiler only needs to know, in other words, that the p.l procedure is calling PLVprs.display_wrap properly -- and that information is contained in the specification. I didn't have a faulty product. I had a faulty installation script!
Take a look at the PLVinst.sql file on your disk. This SQL*Plus script now installs the PL/Vision packages in a more sensible fashion. You will see that there are two phases to the installation of PL/Vision: first, all the package specifications are created, and then package body creation scripts are executed. In this way, I can leverage all the different, handy elements of PL/Vision in other parts of the product.
I learned from this experience that I should always separate the scripts for the creation of the package specification and body, even if the packages are very short and simple.
Copyright (c) 2000 O'Reilly & Associates. All rights reserved.