Not All Best Practices Are Created Equal

Listen Print

Not All Best Practices Are Created Equal

by Steven Feuerstein
04/02/2001

Oracle PL /SQL Best Practices contains about 120 distinct recommendations. I could have included many, many more. In fact, I filled up a Rejects document as I wrote the book. Following the proven, "top-down" approach, I first came up with a list of best practices in each area of the language. Then I went through each area, filling in the descriptions, examples, and so on. As I did this, I encountered numerous "best practices" that surely were the right way to do things. The reality, however, is that few people would ever bother to remember and follow them, and if they did bother, it would not make a significant difference in their code.

I had realized, you see, that not all best practices are created equal. Some are much, much more important than others. And some are just better left out of my book, so that readers aren't distracted by clutter. I hope that the result--my book--has an immediate and lasting impact. But even among the best practices I didn't reject, some stand out as being especially important--so I've decided to award these best practices the following prizes:

Grand Prize: SQL-00: Establish and follow clear rules for how to write SQL in your application.
First Prize: MOD-01: Encapsulate and name business rules and formulas behind function headers.
Second Prize: Two Winners: EXC-00: Set guidelines for application-wide error handling before you start coding.; PKG-02: Provide well-defined interfaces to business data and functional manipulation using packages.
Third Prize: Four Winners: MOD-03: Limit execution section sizes to a single page using modularization.; DAT-15: Expose package globals using "get and set" modules.; DEV-03: Walk through each other's code.; STYL-09: Comment tersely with value-added information.

If you follow each of these "best of the best" practices, described in greater detail below, you will end up with applications that are the joy and envy of developers everywhere!

SQL-00

Establish and follow clear rules for how to write SQL in your application.

Never repeat a SQL statement.
Encapsulate all SQL statements behind a procedural interface (usually a package).
Write your code assuming that the underlying data structures will change.
Take advantage of PL/SQL-specific enhancements for SQL.

All these topics--with examples, benefits, and challenges--are explored in more detail in Chapter 6, "Writing SQL in PL/SQL," of Oracle PL/SQL Best Practices.

MOD-01

Encapsulate and name business rules and formulas behind function headers.

This is one of the most important best practices you will ever read--and, I hope, follow. The one aspect of any software project that never changes is that stuff always changes. Business requirements, data structures, user interfaces: all these things change and change frequently. Your job as a programmer is to write code that adapts easily to these changes.

So whenever you need to express a business rule (such as "is this string a valid ISBN?"), put it inside a subroutine that hides the individual steps (which might change) and returns the results (if any).

Example

Suppose that you must be at least ten years old to borrow books from the library. This is a simple formula and very unlikely to change. I set about building the application by creating the following trigger:

CREATE OR REPLACE TRIGGER are_you_too_young
   AFTER insert OR update 
   ON borrower FOR EACH ROW
BEGIN
   IF :new.date_of_birth > 
         ADD_MONTHS (SYSDATE, -12 * 10)
   THEN
      RAISE_APPLICATION_ERROR (
         -20703, 
        'Borrower must be at least 10 yrs old.');
   END IF;
END;
/

Later, while building a batch-processing script that checks and loads over 10,000 borrower applications, I include the following check in the program:

BEGIN
   ...
   IF ADD_MONTHS (SYSDATE, -122) > rec.date_of_birth 
   THEN
      err.log ('Borrower ' || rec.borrower_id ||
         ' is not ten years old.');
   ELSE
      ...load the data

And so on from there. I am left, unfortunately, with a real job on my hands when I get a memo that says: "The minimum age for a library card has been changed from ten to eight in order to support a new city-wide initiative to increase literacy." And then, of course, there are also the two bugs I introduced into my second construction of the rule. Did you notice them and the inconsistent error messages? The IF statement should read:

IF ADD_MONTHS (SYSDATE, -120) < rec.date_of_birth

If only I had created a simple function the first time I needed to calculate minimum valid age! Something like this:

CREATE OR REPLACE FUNCTION borrower_old_enough (
   dob_in IN DATE)
   RETURN BOOLEAN
IS
BEGIN
   RETURN NVL (
      dob_in < ADD_MONTHS (SYSDATE, -10 * 12),
      FALSE
      );
END;

And now I even check for a NULL value, which I forgot to do in those other programs.

Benefits

You can update business rules and formulas in your code about as quickly and as often as users change everything that was supposedly "cast in stone". Developers apply those rules consistently throughout the application base, since they are simply calling a program.

Your code is much easier to understand, since developers don't have to wade through complex logic to understand which business rule is being implemented.

Challenges

It's mostly a matter of discipline and advance planning. Before you start building your application, create a set of packages to hold business rules and formulas for distinct areas of functionality. Make sure that the names of the packages clearly identify their purpose. Then promote and use them rigorously throughout the development organization.

Steven Feuerstein's utPLSQL Project was launched last year. The utPLSQL Project is an open source unit-testing framework for Oracle PL/SQL developers. It has since undergone major development, including the release of utPLSQL version 1.5.6 in March 2001. Version 1.5.6 includes improved documentation and functionality and a revamped installation procedure.

EXC-00

Set guidelines for application-wide error handling before you start coding.

It's impractical to define EXCEPTION sections in your code after the fact--in other words, after the programs have been written. The best way to implement application-wide, consistent error handling is to use a standardized package that contains at least the following elements:

Procedures that perform most exception-handling tasks, such as writing to an error log.
A raise program that hides the complexity of RAISE_APPLICATION_ERROR and application-specific error numbers.
A function that returns error message text for a given error number.

These ideas are covered in specific best practices in Chapter 5, "Exception Handling."

PKG-02

Provide well-defined interfaces to business data and functional manipulation using packages.

Humans can handle only so much complexity at once. The details and nuances of any decent-size application overwhelm the human mind. Use packages to hide--or at least attempt to organize--the mind-boggling complexity. Expose the underlying data and business rules in an orderly and manageable fashion through the package specification.

This technique is crucially important when implementing core business rules in your application. Every such rule should be hidden behind a function and defined in the appropriate package.

In addition, hide all the SQL for a given table or business entity behind a package interface (this process is called table encapsulation). Rather than write an INSERT statement in your program, call an insert procedure. See SQL-15 in Chapter 6 for more details, and for more on PKG-02, see Chapter 8, "Package Construction."

Example

Let's look at a simple example: building a timing utility. The DBMS_UTILITY.GET_TIME built-in function returns the number of hundredths of seconds that have elapsed since an arbitrary point in time. You call it twice and subtract the difference to calculate elapsed time (down to the hundredth of a second), as in:

DECLARE
   l_start PLS_INTEGER;
   l_end PLS_INTEGER;
BEGIN
   l_start := DBMS_UTILITY.GET_TIME;
   overdue.calculate_fines;
   l_end := DBMS_UTILITY.GET_TIME;
   pl ('Calculated fines in ' ||
       (l_end - l_start) / 100 || ' seconds');
END;

I have two concerns: (a) that's a lot of code to write to simply calculate elapsed time, and (b) the formula is exposed that calculates elapsed time. What if the formula changes? Ah, you're probably asking: How could a formula this simple change? Well, it turns out that this formula can sometimes result in a negative elapsed time, because DBMS_UTILITY.GET_TIME occasionally "rolls over" to zero.

So rather than writing code like that shown in the preceding example, you are much better served by building a simple package as follows:

CREATE OR REPLACE PACKAGE tmr
IS
   PROCEDURE capture;
   PROCEDURE show_elapsed;
END tmr;
/
CREATE OR REPLACE PACKAGE BODY tmr
IS       
   c_bignum INTEGER := POWER(2,32);
   last_timing NUMBER := NULL;
 
   PROCEDURE capture IS
   BEGIN
     last_timing := DBMS_UTILITY.GET_TIME;
   END capture;
 
   PROCEDURE show_elapsed IS
   BEGIN
      pl (MOD (DBMS_UTILITY.GET_TIME - 
                 last_timing + c_bignum, c_bignum));
   END show_elapsed;
END tmr;
/

This package-based implementation now allows you to calculate elapsed time as follows:

BEGIN
   tmr.capture;
   overdue.calculate_fines;
   tmr.show_elapsed;
END;

Benefits

By using packages to hide complexity, you naturally employ stepwise refinement (a.k.a. top-down design). The resulting code is easier to understand, use, and maintain.

By hiding formulas, you can fix them and enhance them as needed over time.

Resources

tmr.pkg: The simplest version of the timer package

PLVtmr.pkg: A more complete implementation

tmr81.ot: An object-based timer

MOD-03

Limit execution section sizes to a single page using modularization.

Sure, you're laughing out loud. You write code for the real world. It's really complicated. Fifty or sixty lines? You're lucky if your programs are less than 500 lines! Well, it's not a matter of complexity; it's more an issue of how you handle that complexity.

If your executable sections go on for hundreds of lines, with a loop starting on page 2 and ending on page 6 and so on, you will have a hard time "grasping the whole" and following the logic of the program.

An alternative is to use step-wise refinement (a.k.a. "top down decomposition"): Don't dive into all the details immediately. Instead, start with a general description (written in actual code, mind you) of what your program is supposed to do. Then implement all subprogram calls in that description following the same method.

The result is that at any given level (PL/SQL block) of refinement, you can take in and easily comprehend the full underlying logic at that level. This technique is also referred to as "divide and conquer."

Example

Consider the following procedure. The entire program might be hundreds of lines long, but the main body of assign_workload (starting with BEGIN /*main*/) is only 15 lines long. Not only that, I can read it pretty much as an exciting novel: "For every telesales rep, if that person's case load is less than their department's average, assign the next open case to that person and schedule the next appointment for that case."

CREATE OR REPLACE PROCEDURE assign_workload 
IS
/* Overview: For every telesales rep, if that person's 
   case load is less than their department's average, 
   assign the next open case to that person and schedule 
   the next appointment for that case. */
   ... declarations of cursors and variables

   -- Local module declarations of full programs   
   PROCEDURE assign_next_open_case (
      telesales_id_in IN NUMBER, case_out OUT NUMBER) 
   IS BEGIN 
      ... full, local implementation;
   END assign_next_open_case;

   FUNCTION next_appointment (case_in IN NUMBER)
      RETURN DATE ... END next_appointment;

   PROCEDURE schedule_case (case_in IN NUMBER, 
      date_in IN DATE) ... END schedule_case;

BEGIN /*main*/
   FOR telesales_rec IN telesales_cur
   LOOP
      IF analysis.caseload (
            telesales_rec.telesales_id) < 
         analysis.avg_cases (
            telesales_rec.department_id);
      THEN
         assign_next_open_case (
            telesales_rec.telesales_id, case#);
         schedule_case (
            case#, next_appointment (case#));
      END IF;
   END LOOP;
END assign_workload;

Benefits

You can implement complicated functionality with a minimum number of bugs by using step-wise refinement. Local modules and packaged programs play a major role in keeping each executable section small.

A developer can understand and maintain a program with confidence if he can read and grasp the logic of the code.

Challenges

You have to be disciplined about holding off writing the low-level implementation of functionality. Instead, come up with accurate, descriptive names for packages, procedures, and functions that contain the implementations themselves.

Resources

Construx Software: Contains lots of good advice on writing modular code.

DAT-15

Expose package globals using "get and set" modules.

Data structures (scalar variables, collections, cursors) declared in the package specification (not within any specific program) are directly referenceable from any program run from a session with EXECUTE authority on the package. This is always a bad idea and should be avoided.

Instead, declare all package-level data in the package body and provide "get and set" programs--a function to GET the value and a procedure to SET the value--in the package specification. Developers can then access the data through these programs, and automatically follow whatever rules you establish for manipulating that data. For more details on DAT-15, see Chapter 3, "Variables and Data Structures."

Example

I've created a package to calculate overdue fines. The fine is, by default, $.10 per day, but it can be changed according to this rule: the fine can never be less than $.05 or more than $.25 per day. Here's my first version:

CREATE OR REPLACE PACKAGE overdue_pkg
IS
   g_daily_fine NUMBER := .10;
 
   FUNCTION days_overdue (isbn_in IN book.isbn%TYPE)
      RETURN INTEGER;
 
   -- Relies on g_daily_fine for calculation
   FUNCTION fine (isbn_in IN book.isbn%TYPE)
      RETURN INTEGER;
END overdue_pkg;

You can easily see the problem with this package in the following block:

BEGIN
   overdue_pkg.g_daily_fine := .50;
 
   pl ('Your overdue fine is ' ||
       overdue_pkg.fine (' 1-56592-375-8'));
END;

As you can see, I bypassed the business rule and applied a daily fine of $.50 ! By "publishing" the daily fine variable, I lost control of my data structure and the ability to enforce my business rules.

The following rewrite of overdue_pkg fixes the problem; for the sake of the trees, I show only the replacement of the g_daily_fine variable with its "get and set" programs:

CREATE OR REPLACE PACKAGE overdue_pkg
IS
   PROCEDURE set_daily_fine (fine_in IN NUMBER);
   PROCEDURE daily_fine RETURN NUMBER;

and the implementation:

CREATE OR REPLACE PACKAGE BODY overdue_pkg
IS
   g_daily_fine NUMBER := .10;

   PROCEDURE set_daily_fine (fine_in IN NUMBER)
   IS
   BEGIN
      g_daily_fine := 
         GREATEST (LEAST (fine_in, .25), .05);
   END;

   FUNCTION daily_fine
      RETURN NUMBER
   IS
   BEGIN
      RETURN g_daily_fine;
   END;

Now it's impossible to bypass the business rule for the daily fine!

Tip: You will be even better off, of course, if you put your maximum and minimum fine information in a database table. Then you can use the package initialization section to load these limits into package data structures. This way, if (when) the data points change, you don't have to change the program itself, just some rows and columns in a table.

Benefits

The only way to change a value is through the set procedure. The values of your data structures are protected; business rules can be enforced without exception.

You can track all accesses to your data structure--that is, you can put a "watch" on a variable. This is a debugging feature that isn't even supported by Oracle's debugger API (as of Oracle8i ).

By hiding the data structure, you give yourself the freedom to change how that data is defined without affecting all accesses to the data.

Package data can now be accessed from Oracle Developer tools, such as Formsbuilder. You may not, from "client-side" PL/SQL (i.e., code written in Oracle Developer components) reference stored package elements unless they are procedures or functions.

Challenges

You need to write get and set programs for your data structures (see the "Resources" section below for help in this matter).

Review existing packages to identify data structures defined in specifications--and then fix them by moving the structures to the bodies. You will have to rewrite some existing programs that reference that data, but it will be worth it.

Resources

overdue.pkg: The overdue package.

PLVgen: The PLVgen package of PL/Vision generates "get and set" code for any scalar variable; this way you won't have to write the logic again and again.

p_and_l.pkg and watch.pkg : Demonstration of "watching" a variable.

DEV-03

Walk through each other's code.

Software is written to be executed by a machine. These machines are very, very fast, but they aren't terribly smart. They simply do what they are told, following the instructions of the software we write, as well as the many other layers of software that control the CPU, storage, memory, etc.

It is extremely important, therefore, that we make sure the code we write does the right thing. Our computers can't tell us if we missed the mark ("garbage in, garbage out" or, unfortunately, "garbage in, gospel out"). The usual way we validate code is by running that code and checking the outcomes (well, actually, in most cases we have our users run the code and let us know about failures). Such tests are, of course, crucial and must be made. But they aren't enough.

It is certainly possible that our tests aren't comprehensive and leave errors undetected. It is also conceivable that the way in which our code was written produces the correct results in very undesirable ways. For instance, the code might work "by accident" (two errors cancel themselves out). For more details on DEV-03, see Chapter 1, "The Development Process."

A crucial complement to formal testing of code is a formalized process of code review or walk-through. Code review involves having other developers actually read and review your source code. This review process can take many different forms, including:

The buddy system: Each programmer is assigned another programmer to be ready at any time to look at his buddy's code and to offer feedback.
Formal code walkthroughs: On a regular basis (and certainly as a "gate" before any program moves to production status), a developer presents or "walks through" her code before a group of programmers.
Pair programming: No one codes alone! Whenever you write software, you do it in pairs, where one person handles the tactical work (thinks about the specific code to be written and does the typing), while the second person takes the strategic role (keeps an eye on the overall architecture, looks out for possible bugs, and generally critiques--always constructively). Pair programming is an integral part of Extreme Programming.

Benefits

Overall quality of code increases dramatically. The architecture of the application tends to be sounder, and the number of bugs in production code goes way down. A further advantage is that of staff education--not just awareness of the project, but also an increase in technological proficiency due to the synergistic effect of working together.

Challenges

The development manager or team leader must take the initiative to set up the code review process and must give developers the time (and training) to do it right. Also, code review seems to be the first casualty of deadline crunch. Further, a new PL/SQL project might not have the language expertise available on the team to do complete, meaningful walkthroughs.

Resources

Handbook of Walkthroughs, Inspections, and Technical Reviews, by Daniel Freedman and Gerald M. Weinberg (Dorset House). Now in its third edition, this book uses a question-and-answer format to show you exactly how to implement reviews for all sorts of product and software development.

Extreme Programming Explained, by Kent Beck (Addison-Wesley). The first book on "Extreme Programming" offers many insights into pair programming.

Extreme Programming Installed, by Ron Jeffries, Ann Anderson, and Chet Hendrickson (Addison-Wesley). Focuses on how to implement Extreme Programming in your environment. For more information, you can also go to XProgramming.com.

STYL-09

Comment tersely with value-added information.

The best way to explain what your code is doing is to let that code speak for itself. You can take advantage of many self-documentation techniques, including:

Define variables and call programs (local modules, in particular; see MOD-03 in Chapter 7, "Program Construction") to give names to and hide complex expressions.
Use the language construct that best reflects the code you are writing (declare CONSTANTS when values don't change, choose the right kind of loop for your logic, etc.).

Whenever you find yourself adding a comment to your code, first consider whether it is possible to modify the code itself to express your comment. Good reasons to add comments include:

Program headers (see STYL-03 in Chapter 2, "Coding Style and Conventions")
Explanations of workarounds, patches, operating-system dependencies, and other "exceptional" circumstances
Complex or opaque logic

Example

Let's follow a trail of unnecessarily commented code to self-documenting code. I start with:

/* If the first field of the properties record is N... */
IF properties_flag.field1 = 'N'

Yikes! My line of code was incomprehensible and my comment simply repeated the code using the English language, rather than PL/SQL. No added value, no real assistance, yet not at all uncommon. The least I can do is use the comment to "translate" from computer-talk to business requirement:

/* If the customer is not eligible for a discount... */
IF properties_flag.field1 = 'N'

That's better, but I have created a redundancy: if my requirement ever changes, I have to change the comment and the code. Why not change the names of my variables and literals so that the code explains itself?

IF customer_flag.discount = constants.ineligible

Much better! Now I no longer need a comment. My remaining concern with this line of code is that it "exposes" a business rule; it shows how (at this moment in time) I determine whether a customer is eligible for a discount. Business rules are notorious for changing over time--and for being referenced in multiple places throughout my application. So my best bet is to hide the rule behind a self-documenting function call:

IF NOT customer_rules.eligible_for_discount (customer_id)

Benefits

By emphasizing reliance on code and not comments to explain, your program becomes more concise and more readable.

When business requirements change, you don't have to change the code and the comment that explained the code.

The business rule is likely to be reused in many other places in your application (see MOD-01).

Challenges

It can be difficult to recognize formulas and business rules (especially when you have been asked to maintain or modify someone else's programs, or when you are new to an application).

Once you recognize an exposed formula, you have to be careful about extracting it from the code and replacing it with a variable or program call.

Steven Feuerstein is considered one of the world's leading experts on the Oracle PL/SQL language. He is the author or coauthor of Oracle PL/SQL Programming, 2nd Edition, Oracle PL/SQL Programming: Guide to Oracle8i Features, Oracle PL/SQL Developer's Workbook, Oracle Built-in Packages, Advanced Oracle PL/SQL Programming with Packages, and several pocket reference books (all from O'Reilly & Associates). Steven is a Senior Technology Advisor with Quest Software, has been developing software since 1980, and worked for Oracle Corporation from 1987 to 1992.

Steven hosts the PL/SQL Pipeline, an online community for PL/SQL developers and contributes to RevealNet's Active PL/SQL Knowledge Base. He offers training and consulting on software development and the Oracle PL/SQL language through PL/Solutions.

In matters pertaining to humanity rather than programming, Steven currently serves as president of the Board of Directors of the Crossroads Fund, which makes grants to Chicagoland organizations working for social, racial, environmental, and economic justice. He is also active in Not In My Name, a gathering of Jews who seek a lasting and just peace between Israelis and Palestinians. You can reach Steven at steven@stevenfeuerstein.com.

O'Reilly & Associates will soon release (April 2001) Oracle PL/SQL Best Practices.

Sample Chapter 4, Control Structures, is available free online.
You can also look at the Table of Contents, and the Full Description of the book.
For more information, or to order the book, click here.

Return to: oracle.oreilly.com