Skip to Main Content
Information Technology Services

PL/SQL Programming Guidelines

Now that naming standards are defined, here are some general guidelines for good programming practices. Most them are universal and would apply to any type of a programming effort. But we are only speaking in terms of PL/SQL here.

Use Named Constants to Avoid Hard-coding Values

Follow these simple guidelines regarding literals in all of your applications:

  1. Remove all literals (within reason) from your code. Instead, declare constants, which hold those literal values.
  2. Allow the value of that literal (now a constant) to be set in only one place in your code, preferably with call to a procedure. This procedure can be run on start-up of the application, or from the initialization section of a package.
  3. Provide a single way to retrieve the literal value, preferably through a call to a function. Don't let any program reference the literal directly. This way you reserve the right and ability to change at any time the data structures you use to store that literal -- without affecting any of the programs which rely on that constant.

Convert Variables into Named Constants

If you find that you have written a program in which a variable's value does not change, you should first determine if that behavior is correct. If all is as it should be, you should then convert that variable to a constant.

Why should you bother converting "read only" variables to constants (and named ones at that)? Because when you "tell it and use it like it is," the program explains itself more clearly. The declaration of a named identifier as a constant gives you information about how it should be used in the program.

If you do convert a variable to a constant, you should also change its name. This will help to remind anyone reading the code that your identifier refers to a constant and cannot be changed.

Avoid the recycling of variables

Each variable and constant you declare should have one purpose and one purpose only. The name for that variable or constant should describe, as clearly as possible, that single-minded purpose.

The only reason to create generically named variables and constants is to save you, the developer, typing time. That is always a terrible reason for a coding style or change in a programming effort. Reliance on a "time-saver" short cut should raise a red flag: you are probably doing (or avoiding) something now for which you will pay later.