Too much of a good thing?

I remember one fateful day when I found the following line of PL/SQL code at a client site

g_period CONSTANT VARCHAR2(1) := '.';

Constants are great. They allow you to rely on a persistent value for processing. They are also really good if you need to use a value again and again in code, the value must be the same throughout the code, and if the value could change due to new requirements, change in direction, etc. For example, your code uses the following constant to denote a value of "True"

g_true CONSTANT VARCHAR2(4) := 'TRUE';

At some later point it is decided that the value should simply be the letter 'T' and not 'TRUE'. If you've used the constant throughout your code it's as simple as changing the value of the constant in one place - where it's declared. Quick and easy. That's just good programming.

g_true CONSTANT VARCHAR2(1) := 'T';

The g_period example listed above is a little overboard. All the programmer had accomplished was declared a constant of something that is already a constant! If we followed that example we might as well declare every character as a constant and use them everywhere in the code.

g_c CONSTANT VARCHAR2(1) := 'C';
g_h CONSTANT VARCHAR2(1) := 'H';
g_r CONSTANT VARCHAR2(1) := 'R';
g_i CONSTANT VARCHAR2(1) := 'I';
g_s CONSTANT VARCHAR2(1) := 'S';

Now I can spell my name and never worry about the letters changing!

DECLARE
  l_my_name VARCHAR2(5);
BEGIN
  l_my_name := g_c||g_h||g_r||g_i||g_s;
END;
/

It looks a little silly when it's put like that, doesn't it?

Using constants properly can save you a lot of pain, and using them improperly can cause just as much pain as you may save. Food for thought.

Happy coding.