Advanced Oracle PL/SQL Programming with Packages

Advanced Oracle PL/SQL Programming with PackagesSearch this book
Previous: 2.2 Using Effective Coding Style for PackagesChapter 2
Best Practices for Packages
Next: 2.4 Organizing Package Source Code

2.3 Selecting Package Names

Have you ever noticed that a package is never executed or referenced? The package is only a container for all the elements inside the package. In your code you will execute package-based procedures and functions. You will reference package-based constants, variables, cursors, and so on. Consequently, all references to package-based elements are accomplished with qualified notation: package.element. You should take this format into account when you name both the package and the elements within a package.

In this section I discuss the following aspects of naming package-based elements:

If you follow the advice in this section, you will design packages that are more easily used and understood by other developers.

2.3.1 Choosing Appropriate and Accurate Names

As a rule, developers are much too careless about the names they give to their packages and the elements inside those packages, (most importantly, procedures and functions.) There are two aspects to coming up with the right names for your code elements:

Have you ever thought about the structure of the names you choose? PL/SQL is a computer language. It is much simpler than human languages like Japanese or English, but it still has many of the same grammatical components, such as nouns and verbs. RAISE_APPLICATION_ERROR, for example, contains a verb (RAISE)-noun (APPLICATION_ERROR) combination, as in: "Raise this error." The built-in function, SUBSTR, is an example of a noun (SUBSTR), as in: "if the substring is NULL, then ask for a dollar amount."

PL/SQL on the other hand, is more complicated than human languages because you, the developer, get to make up words in the language as you go. You define new nouns and verbs every time you declare a variable or define a new program. This means that each and every one of us is, at least in part, responsible for the integrity of the PL/SQL language. Keep this in mind as you name your program elements. Let's apply this consideration to packages.

First of all, the name of the package should always be structured as a noun. The package itself does not do anything, so it cannot and should not be an action verb. The package name declares, as simply as possible, the contents of the package. If you are writing a package to analyze sales, the name of the package should be something like:


and not either of these:


It should also probably not be something as vague as "sales". There are many different aspects to sales; there would be no way to tell from the name that this package performs analyses on sales figures.

Beyond the package name itself, you must be very careful in your naming of elements within the package. A procedure is an executable statement, a command to the PL/SQL compiler. Consequently, the structure of the procedure name should be similar to a command:


as in:


A function, on the other hand, is used like an expression in an executable statement. Because it returns, or represents, a value, the structure of a function name (as well as all constants and variables) should also be a noun:


as in:


If I use the wrong grammatical structure for my names, they do not read properly.

2.3.2 Avoiding Redundancy

Keep in mind that when you reference a package element outside of the package you must use dot notation (package.element). As a result, you will want to avoid redundancy in your package and element names. For example, suppose I have a package named emp_maint for employee maintenance. One of the procedures in the package sets the employee salary.

Here is a redundant naming scheme:

PACKAGE emp_maint
   PROCEDURE set_emp_sal;

With this approach, I would then execute the procedure as follows:


I do not need to mention emp again in the procedure name. The entire package is all about maintaining employees. That should be assumed in the names of all elements defined within the package. A more sensible approach would be:

PACKAGE emp_maint
   PROCEDURE set_sal;

With this new approach, I can then execute the procedure as follows:


In this way, I type less and the resulting code is more readable.

2.3.3 Avoiding Superfluous Naming Elements

I often recommend that you include as a suffix or prefix to an element name an abbreviation that indicates clearly the type of element. So whenever I declare a cursor, for example, I always append a suffix of "_cur" as shown in the example below:

    CURSOR emp_cur IS
       SELECT ...;
    FOR emp_rec IN emp_cur

You can go overboard with these abbreviations and end up with names that are unwieldy and trip over themselves. The package name is one of those instances. I recommend that you do not append suffixes like "pkg" or "pak" to the names of packages. It will be clear enough from the way the packaged elements are referenced and used that they are defined within a package. Let's look at an example to illustrate the point.

Suppose I define my emp_maint package as follows:

PACKAGE emp_maint_pak
   PROCEDURE set_sal;

With this verbose approach, I then execute the procedure as follows:


What do I gain by including the "pak" in the call, except to add to my typing? There can be no doubt at all that the set_sal procedure is defined within a package.

Similarly, I have worked at companies whose naming conventions dictate that whenever you create a procedure, you must preface the name with "pr" as in:


Function names must, of course, be prefaced with "fu" as in:

v_totsal := fu_total_salary;

This is serious overkill; if you have conventions like these, you need to find a better balance between self-documentation, readability, and productivity.

Some readers may notice an inconsistency in my approach to using suffixes. I suggest that you do not include "pkg" in your package names. I do continue to recommend, on the other hand, that you use a suffix for cursors and records, such as emp_cur and obj_rec. Why not drop the suffix for all of these elements? After all, it is usually pretty clear when I refer to a cursor or record. The clearest justification has to do with avoiding name duplication within the same PL/SQL block scope. Package names must be unique within an Oracle account. Within a single package, however, you may well want to define records, cursors, PL/SQL tables, programmer-defined record TYPEs, and so on for, say, the emp entity. If you do not use standard suffixes (or prefixes), you will end up with a bewildering variety of names. Conventions based on the entity name -- such as emp or dept or orders -- offer the simplest and clearest way to distinguish between these different elements of the PL/SQL language.[1]

[1] Thanks to John Beresniewicz for this insight.

Previous: 2.2 Using Effective Coding Style for PackagesAdvanced Oracle PL/SQL Programming with PackagesNext: 2.4 Organizing Package Source Code
2.2 Using Effective Coding Style for PackagesBook Index2.4 Organizing Package Source Code

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