Oracle PL/SQL Best Practices

Name

DAT-12: Package application-named literal constants together.

Synopsis

Never place a hard-coded literal, such as “Y” or 150 in your code. Instead, create a package to hold these values and publish a name to be used in place of the literals. You will probably find it best to:

• Define constants that are referenced throughout your application in a single, central package.

• Define constants that are more specific to a single area of functionality within the package that encapsulates that functionality.

Example

Here is a portion of a general constants package:

CREATE OR REPLACE PACKAGE constants
IS
   -- Standard string representation of TRUE/FALSE
   tval CONSTANT CHAR(1) := 'T';
   fval CONSTANT CHAR(1) := 'F';

   -- Earliest valid date: 5 years past
   min_date CONSTANT DATE := 
      ADD_MONTHS (SYSDATE, -5 * 12);

And here is a package that contains constants specific to one area of functionality:

CREATE OR REPLACE PACKAGE nightly_transform
IS
   c_max_weeks CONSTANT INTEGER := 54;

   c_active CONSTANT CHAR(1) := 'A';
   c_inactive CONSTANT CHAR(1) := 'I';

   c_english CONSTANT INTEGER := 1;
   c_usa CONSTANT INTEGER := 1;
   c_namerica CONSTANT VARCHAR2(2) := 'N';
END nightly_transform;

Benefits

You’re less likely to hard-code literal values in your programs, thus improving the readability and maintainability of your code.

Youve established a place to go when a developer needs to add another constant to hide a literal.

Challenges

The entire development team needs to know about the packages and use the constants that have been ...