Advanced Oracle PL/SQL Programming with Packages

Advanced Oracle PL/SQL Programming with PackagesSearch this book
Previous: 2.3 Selecting Package NamesChapter 2
Best Practices for Packages
Next: 2.5 Constructing the Optimal Interface to Your Package
 

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 SQL*Plus script (SQL statement plus SQL*Plus 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 SQL*Plus script (SQL statement plus SQL*Plus 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:

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.


Previous: 2.3 Selecting Package NamesAdvanced Oracle PL/SQL Programming with PackagesNext: 2.5 Constructing the Optimal Interface to Your Package
2.3 Selecting Package NamesBook Index2.5 Constructing the Optimal Interface to Your Package

The Oracle Library Navigation

Copyright (c) 2000 O'Reilly & Associates. All rights reserved.

Library Home Oracle PL/SQL Programming, 2nd. Ed. Guide to Oracle 8i Features Oracle Built-in Packages Advanced PL/SQL Programming with Packages Oracle Web Applications Oracle PL/SQL Language Pocket Reference Oracle PL/SQL Built-ins Pocket Reference