home | O'Reilly's CD bookshelfs | FreeBSD | Linux | Cisco | Cisco Exam    

2.2 Using Effective Coding Style for Packages

A package is a collection of PL/SQL elements, including data structures (from cursors to constants) and program units (procedures and functions). Packages are generally the most complicated and extended pieces of code PL/SQL developers will write. To make matters worse, the current array of PL/SQL development environments do not offer any tools for viewing and managing a package as a collection. A package is treated and presented no differently from a single function -- just a whole bunch of lines of source code.

As a result, it is up to you to design and write your package to make it as readable and maintainable as possible. There are two fundamental strategies you can employ to help meet this objective:

Strategy 1: Use all available techniques to make your code as clean, modular, and structured as possible.

Strategy 2: Come up with a consistent coding style and format for your packages -- and get people to follow that style.

Many of the other best practices covered in this chapter address the first strategy -- which is clearly the more important and difficult of the two. In this section, I suggest elements of a coding style for packages. It is absolutely critical that you adopt an effective coding style and employ it consistently. This style should be compatible, of course, with the style you use throughout your PL/SQL code. It should also, however, include components that reflect and support the structure and significance of the package.

The most basic elements of a package style are, first of all, no different from the style I encourage for all other kinds of PL/SQL code. These elements include:

  • Use consistent indentation to reveal the logical flow of the program and to delineate the different sections of the PL/SQL program structure. Generally, this means that all executable statements are indented in from the BEGIN keyword, the body of a loop is indented within the LOOP and END LOOP keywords, and so on. Within a package, all specification declarations are indented between the IS and END keywords.

  • Code all reserved words in the PL/SQL language in upper-case. Use lower-case for all application-specific identifiers. Generally, this is accomplished with hard-coded literals and the use of UPPER and LOWER. This guideline presents more of a challenge when applied to complex expressions passed to PLVgen as default values, as we'll see later.

  • Use comments to add value to the code. Don't bother with comments that simply repeat what the code clearly states.

The style elements I find valuable particularly for packages include the following:

  • Use banners (specially formatted comment lines) to mark clearly the different groupings of package elements.

  • Use end labels for the package and for all program units defined in the package body.

The best way to demonstrate these coding styles is to show you the template I use for package construction. I have been writing a lot of PL/SQL code in the past year and I found myself typing the same words and phrases over and over again. To improve my productivity and also the consistency of my code, I built a package called PLVgen to generate PL/SQL programs (see Chapter 15, PLVvu: Viewing Source Code and Compile Errors ). Example 2.1 shows the basic template of a package generated with PLVgen.

NOTE: The PLVgen.pkg procedure also generated the line numbers to go with the source code.

Example 2.1: A Generated Package Template

SQL> exec 

  2 /*
  3 || Program: emp_maint
  4 ||  Author: Steven Feuerstein
  5 ||    File: emp_maint.SQL
  6 || Created: APR 13, 1996 18:56:59
  7 */
  8 /*HELP
  9 Add help text here...
 10 HELP*/
 13 Add help text here...
 16 IS
 17 /* Public Data Structures */
 19 /* Public Programs */
 21    PROCEDURE help (context_in IN VARCHAR2 := NULL);
 23 END emp_maint;
 24 /
 27 IS
 28 /* Private Data Structures */
 30 /* Private Programs */
 32 /* Public Programs */
 34    PROCEDURE help (context_in IN VARCHAR2 := NULL)
 35    IS
 36    BEGIN
 37       PLVhlp.show ('s:emp_maint', context_in);
 38    END help;
 39 END emp_maint;
 40 /

There are several features I would like to highlight in my package template:




A standard header for the package, showing the author, filename, and date created.


Stubs for help text. I have developed an architecture (and the PLVhlp package, described in Chapter 16, PLVgen: Generating PL/SQL Programs ) to provide online help for PL/SQL programs. These comment blocks provide both inline code documentation and help text to users.


Banners to identify the two main kinds of elements that can appear in a package specification: data structures and program units.


Header for a procedure that delivers online help for this package. Of course, this should only be included if the online help package is being used.


The END statement with the package name appended.


Banners to identify the three kinds of elements that can appear in a package body: private data structures, program units, and the implementation of the public program units.


The implementation of the help procedure. Notice that the procedure uses an end label with the program name and is also indented in multiple steps from the overall package.

The emp_maint package shown in Example 2.1 contains the most important elements of a package's "look and feel." All elements declared in the specification are indented in from the package definition statement. They exist within the context of the package, and that relationship is made clear through the indentation. The same rule holds true in the package body (you can see this with the definition of the help procedure). The banner comment lines, on the other hand, are left-justified to match the margin of the package itself. I do this to make sure that these boundary markers stand out as you scan the code.

The banners identifying the different sections become very critical when the package is full of many different elements and runs to hundreds or thousands of lines. They also provide an internal guide during development. As you write a new package program, you may find that you need to create another private variable or private function. If you have the banners in place, you can easily perform a search and then drop this new element into its rightful spot. The alternative (throwing the code in at whatever point of the package you happen to be coding) results in a very chaotic package that is difficult to follow and maintain.

As I make clear in the way I created Example 2.1 , you can use PLVgen.pkg to generate a package with this (or a modified) format.

2.2.1 Choosing the Order of Elements

As with the declaration sections of procedures and functions, you must (both in the package specification and body) declare all variables and data structures before you declare any program units. But what about the order of these program units themselves? As you can see from my banners, I always try to define all my private modules before any of my public modules. These are the building blocks used by the public programs. I group them together so they are easier to locate.

Is this ordering strictly necessary? Yes and no. Yes, you must define a private program before it is referenced by another program in the package (public or private). No, you do not have to group them together. You could instead define all private modules just before they are used by their public counterparts. This can make sense if the private program is only used by a single public program. If it is shared by many public programs (or other private ones, for that matter), then this placement does not accurately reflect its role in the package.

You can, by the way, place the definitions of the public program units anywhere in the package body (after the variable declarations) -- even after they are referenced by another program. How is this possible? Since their headers have already been established in the package specification, the PL/SQL compiler has all the information it needs to resolve the reference.

Previous: 2.1 Starting With Packages Advanced Oracle PL/SQL Programming with Packages Next: 2.3 Selecting Package Names
2.1 Starting With Packages Book Index 2.3 Selecting Package Names

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