[Chapter 2] 2.4 Organizing Package Source Code

2.4 Organizing Package Source Code

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.

File Extension	Description
`procname` .sp	The definition of a stored procedure.
`funcname` .sf	The definition of a stored function.
`pkgname` .spp	A stored package definition that contains the code for both the package specification and the package body.
`pkgname` .sps	The definition of a stored package specification only.
`pkgname` .spb	The definition of a stored package body only.
`scriptname` .sql	An anonymous PL/SQL block or a SQLPlus script ( SQL statement plus SQLPlus commands).
`procname` .wp	The wrapped[ 2 ] definition of a stored procedure.
`funcname` .wf	The wrapped definition of a stored function.
`pkgname` .wpp	A stored package definition that contains the wrapped code for both the package specification and the package body.
`pkgname` .wps	The wrapped definition of a stored package specification only.
`pkgname` .wpb	The wrapped definition of a stored package body only.
`scriptname` .sql	An anonymous PL/SQL block or a SQLPlus script ( SQL statement plus SQLPlus commands).

[2] As of PL/SQL Release 2.2, you can "wrap" your PL/SQL source code into an encrypted format. This format can be compiled into the database, but is not readable. Wrapped code is primarily of value to third-party vendors who provide PL/SQL -based applications, but are not interested in letting the competition see how they built their applications.

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:

Minimize the need to recompile programs. If you recompile a package specification, then any program that references an element in that package will need to be recompiled (its status in the USER_OBJECTS view is set to INVALID). If you recompile only the body, on the other hand, none of the programs calling that package's element are set to invalid. So if you change a package's body and not its specification, you should not recompile the specification -- which means that you should keep those elements of the package in different files.
Allow different packages to depend upon each other. This codependency of packages is explored below.

2.4.1 Creating Codependent Packages

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.

NOTE: This separation of specification and body will not work in all codependent situations. If the specification of package A references an element in package B, and the specification of package B references an element in package A, you will not be able to compile these two packages. Each specification requires the other specification to be previously defined, which simply isn't possible.