The Magic Kingdom

Choose the best way to manage literal values.

May/June 2009

What is the best way to avoid hard-coding literal “magic values” in my PL/SQL-based applications?

Every application has its own set of “magic values”—literals that express some characteristic of that application. These values are generally referenced in many places in code. Sometimes the values are fixed for the lifetime of an application; sometimes they change periodically (with each new year, for example).

Let’s look at an example. Suppose that one rule in my application is that the maximum salary allowed in the employees table is $1,000,000. I might then reference this value in a subprogram:

IF l_new_salary > 1000000
THEN
  RAISE_APPLICATION_ERROR (-20001,
'Maximum salary of 1000000 exceeded!');
END IF;

Most programmers know that referencing a magic value in this way is a bad idea; here I have “hard-coded” the literal (1000000) directly in my subprogram—and more than once. That’s problematic, because inevitably that value will change. When it does, I (or, more accurately, the entire development team) will have to scour the code, changing the old value to the new value.

That is why we all should avoid such hard-coding of literals. So how can we do that? Here are three possibilities:

• Create a packaged constant, assigning the value to that constant.
• Create a function that hides and returns the magic value.
• Store and manage the magic value in a database table.

I will demonstrate each of these approaches and then finish up my answer with a comparison of the options as well as issues you should consider before you choose your own approach.

Create a packaged constant. The essential technique for avoiding hard-coding is to reference your magic value by name. The simplest way to do this is to create a package and define a constant in it to hold the value (or add the constant to an existing package). Here is an example:

PACKAGE magic_values
IS
  c_maximum_salary
      CONSTANT NUMBER := 1000000;
END magic_values;

Note that if you do not include the CONSTANT keyword in the declaration of the magic value, its value will be changeable, which is certainly not constant .

With this package defined, I can now reference it:

IF
   l_new_salary >
      magic_values.c_maximum_salary
THEN
  RAISE_APPLICATION_ERROR (-20001,
    'Maximum salary of ' ||
    magic_values.c_maximum_salary ||
    ' exceeded!');
END IF;

Create a function that hides and returns the magic value. I can also create a function that returns the magic value, and I would recommend always placing this function within a package. Here is an example:

PACKAGE magic_values
IS
   /*
   You cannot earn more than this
   in a single year.
   */
   FUNCTION maximum_salary
      RETURN NUMBER;
END magic_values;
PACKAGE BODY magic_values
IS
   FUNCTION maximum_salary
      RETURN NUMBER
   IS
   BEGIN
      RETURN 1000000;
   END;
END magic_values;

and now my application code will look like this:

IF
   l_new_salary >
      magic_values.maximum_salary ()
THEN
   RAISE_APPLICATION_ERROR (-20001,
    'Maximum salary of ' ||
    magic_values.maximum_salary () ||
    ' exceeded!');
END IF;

Note that I have put the open and close parentheses “()” after the call to the function. This is optional, but it is a way to self-document that you are calling a function and not referencing a variable or a constant.

Store and manage the magic value in a database table. Suppose you have many magic values in your application and are concerned about making sure that they are handled consistently without any hard-coding. You might consider creating a database table to store and manage all these magic values. Listing 1 shows an example of such a table, named magic_values.

Code Listing 1: magic_values table description

TABLE magic_values
(
   name                         VARCHAR2 (100) UNIQUE
 , description                 VARCHAR2 (4000)
 , value                         VARCHAR2 (4000)
 , identifier                   VARCHAR2 (100) UNIQUE
 , datatype                    VARCHAR2 (100) DEFAULT 'VARCHAR2(32767)'
 , function_return_type   VARCHAR2 (100)
)

In the magic_values table, the name column is for the name of the magic value, such as “Maximum salary allowed in company.” The description should be obvious. The value is the magic value itself. The identifier, datatype, and function_return_type columns are used to generate a magic values package. Suppose I put the following data into the magic_values table:

BEGIN
   INSERT INTO magic_values (
     name
     , description
     , VALUE
                  )
       VALUES (
     'Maximum salary'
     , Too much money!'
     , '1000000'
              );
   COMMIT;
END;

I can then build a get_magic_values package, shown in Listing 2, with a set of functions for retrieving a value for a given name. Note that I use a different named function for each datatype, because I cannot use overloaded subprograms whose signatures differ only in the type of data returned. I also use conditional compilation to enable the use of the function result cache when I compile this package on Oracle Database 11g. That will improve performance of retrieval of these values, which will very rarely change.

Code Listing 2: get_magic_values package specification

PACKAGE get_magic_values
IS
   FUNCTION varchar2_value (NAME_IN IN magic_values.name%TYPE)
      RETURN varchar2
      $IF dbms_db_version.ver_le_10_2
      $THEN
        /* No result cache available prior to Oracle Database 11
g */
      $ELSE
         RESULT_CACHE
      $END
      ;
   FUNCTION date_value (NAME_IN IN magic_values.name%TYPE)
      RETURN date
      $IF dbms_db_version.ver_le_10_2
      $THEN
        /* No result cache available prior to Oracle Database 11
g */
      $ELSE
         RESULT_CACHE
      $END
      ;
   FUNCTION number_value (NAME_IN IN magic_values.name%TYPE)
      RETURN number
      $IF dbms_db_version.ver_le_10_2
      $THEN
         /* No result cache available prior to Oracle Database 11
g */
      $ELSE
         RESULT_CACHE
      $END
      ;
END get_magic_values;

Listing 3 shows the implementation of the number_value function only, because that is what I’ve used in this column.

Code Listing 3: get_magic_values package body, with number_value function only

PACKAGE BODY get_magic_values
IS
   FUNCTION number_value (NAME_IN IN magic_values.name%TYPE)
       RETURN NUMBER
       $IF dbms_db_version.ver_le_10_2
       $THEN
           /* No result cache available prior to Oracle Database 11
g */
       $ELSE
           RESULT_CACHE RELIES_ON ( magic_values )
       $END
   IS
       l_value   NUMBER;
   BEGIN
       SELECT VALUE INTO l_value
          FROM magic_values
         WHERE name = NAME_IN;
       RETURN l_value;
   END number_value;
END get_magic_values;

With the get_magic_values package defined and the data inserted, I can rewrite my IF statement that references maximum salary, as shown in Listing 4.

Code Listing 4: IF statement with two references to “Maximum salary”

IF l_new_salary > get_magic_values.number_value ('Maximum salary')
THEN
   RAISE_APPLICATION_ERROR (-20001,        'Maximum salary of ' || get_magic_values.number_value ('Maximum salary') || ' exceeded!');
END IF;

Now, unfortunately, I have hard-coded the name of the value (“Maximum salary”) twice. See Listing 5 for a better solution.

Code Listing 5: Improved IF statement

DECLARE
   c_max_salary CONSTANT NUMBER
           := get_magic_values.number_value ('Maximum salary') ;
   l_new_salary   NUMBER := get_the_salary ();
BEGIN
   IF l_new_salary > c_max_salary
   THEN
      RAISE_APPLICATION_ERROR (-20001
        ,  'Maximum salary of ' || c_max_salary || ' exceeded!');
   END IF;
END;

Another way to use the magic values defined in a table is to generate a package of constants or functions from the rows of the table. The magic_values.sql script, available with the download for this column, offers such a generator, the gen_magic_values_package procedure.

To generate such a package, this procedure needs to know the valid PL/SQL identifier for this magic value, the datatype of the magic value, and (when relying on functions to retrieve the value) an unconstrained version of that datatype to serve as the return type of the function. That’s where those other columns of the magic_values table come into play.

Suppose I now populate the magic_values table, providing values for all columns as shown in Listing 6.

Code Listing 6: Populating the magic_values table

BEGIN
   INSERT INTO magic_values
      VALUES (
                   'Maximum salary'
                 , 'You cannot earn more than this in a single year.'
                 , '1000000'
                 , 'maximum_salary'
                 , 'NUMBER'
                 , NULL
                );
   INSERT  INTO magic_values
      VALUES (
                   'Earliest supported date'
                 , 'The application will not work with dates before this.'
                 , 'ADD_MONTHS (SYSDATE, -60)'
                 , 'minimum_date'
                 , 'DATE'
                 , NULL
                );
   INSERT INTO magic_values
      VALUES (
                   'Open status'
                 , NULL
                 , '''OPEN'''
                 , 'open_status'
                 , 'VARCHAR2(4)'
                 , 'VARCHAR2'
                );
   COMMIT;
END;

I then call the generator procedure:

BEGIN
  gen_magic_values_package (
    pkg_name_in => 'GET_MAGIC_VALUES'
  , hide_values_in_body_in => FALSE
  , dir_in => NULL
  , ext_in => NULL
  );
END;

and my screen (through server output) displays the package specification:

CREATE OR REPLACE PACKAGE
get_magic_values
IS
  /*
  You cannot earn more than this
  in a single year.
  */
  maximum_salary NUMBER
  DEFAULT 1000000;
  /*
  The application will not work
  with dates before this.
  */
  minimum_date DATE
  DEFAULT ADD_MONTHS (SYSDATE, -60);
  open_status VARCHAR2 (4)
  DEFAULT 'OPEN';
END get_magic_values;

These approaches can all help you manage magic values in applications. Which should you use?

The first issue is whether to use a package of constants or a package of functions. Using the function approach, in which the value is hidden inside the package body, has three advantages.

First, developers cannot easily see the actual value as they build their code. This can be advantageous, because it avoids quick-and-dirty programming. Developers can still display the value returned by a call to the function, but that is probably so much trouble that most won’t bother to do this as they build their programs.

The second advantage to using a function approach is that if you ever need to change the value, you change the package body and not the specification. Consequently, the status of program units that are dependent on this package will not be set to INVALID and will not need recompilation. Avoiding recompilation of code under development is helpful, but it is even more important to avoid recompilation of code running in production. Production DBAs will usually need to schedule a planned outage when code upgrades that force recompilation are necessary.

The third advantage to using a function approach is that you can reference the function directly from within an SQL statement, even when that SQL statement is not executed from within a PL/SQL block. A PL/SQL constant cannot be used inside SQL unless it is executed from within a PL/SQL block.

On the other hand, a package of constants is the simplest and most efficient means of delivering the magic value. However, if you choose the package-of-constants approach, be aware that if you change the value of that constant, the package in which it is declared will be invalidated, forcing recompilation of all dependent program units. This is true even in Oracle Database 11g, with its fine-grained dependency enhancements, although recompilation will be limited to only those units that depend on the specific constant that was changed.

One thing to note: in Oracle Database 11g, a package variable declared without the CONSTANT keyword does not set up fine-grained dependencies. However, although you could therefore recompile a package that declares such values without causing cascading invalidation, doing so would be at odds with my earlier statement about declaring a magic value without CONSTANT: “. . . its value will be changeable, which is certainly not constant .”

Assuming that you place your constants inside a package, should you use one central package in your application for all constants or spread them out across multiple packages?

A single package is easier to maintain, but it also creates a possible bottleneck in your application, in that a recompilation of that package could cause the invalidation of many (perhaps most) of your program units. Again, this will not be an issue in Oracle Database 11g.

You might also consider “localizing” your constants into existing packages with related functionality. For example, if you already had a package that contained subprograms related to compensation, the maximum salary constant (or function) would naturally belong there. It’s where developers would intuitively look to find salary-related magic values.

The approach that requires the most effort is constructing a magic values table that contains the specifications of each of the required magic values. It also offers two key advantages. First, all developers can quickly and easily see which magic values have already been defined, reducing the possibility of redundantly creating constants or functions to hide the values. And, rather than manually maintaining the package of constants, you can generate it directly from the contents of the table.

If you take this approach, I suggest that you build a simple user interface for the magic values table, perhaps with Oracle Application Express, so that the table can be managed more easily.

Every application will have its magic values, and many of those values will change over time. It is the responsibility of every developer to ensure that these values do not appear as hard-coded literals throughout the application code base, reducing the maintainability of that code. Whichever path you take, your application quality will improve and your codevelopers with thank you.