[Chapter 3] 3.12 Obliterating the Literals

3.12 Obliterating the Literals

There are still two things that bother me about the repeated function: first, the function is not defined in a package and, second, a user of repeated has to know the correct literals to pass to it to get the right kind of conversion action. On general principles, I believe that everything built in PL/SQL should be placed inside a package. This construct is the cornerstone of programming in the PL/SQL language and offers many advantages, explored in detail throughout this book. My second concern about literals can be answered by creating a package -- so I will show you how to convert the standalone repeated function into a package.

I do not believe that a user of my code should have to remember the specific characters to pass in a string literal. Is it UL or BS? Is it n for "no action" or l for "leave alone"? With the function as implemented throughout this chapter, there is no way for a developer to know at compile time if she called repeated properly.

Beyond this difficulty, applications the world over would be much better off if their creators avoided the use of hard-coded literals in their code. Every time the repeated function is called, some string literal is being hard-coded into a program. If the repeated function is ever modified to expand the scope of action and different literals are used, all those other programs could go haywire. A much better approach would provide named constants in place of the hard-coded strings so that (a) at compile time a developer would know if the call to the function is correct and (b) the actual string values for the action codes can be hidden from view -- and changed as often as is necessary.

The best way (really, the only way) to create named constants for use throughout a PL/SQL application is to put these constants -- and the code with which they are used -- into a package. The stg package shown in Example 3.11 offers the same functionality as the repeated function, with the additional benefit of named constants. Now instead of having a standalone repeated function, I have a dup function in the stg package, and the following constants:

stg.ul: Indicates that you want UPPER-lower case conversion
stg.lu: Indicates that you want lower-UPPER case conversion
stg.n: Indicates that you do not want any case conversion

So when I want to duplicate or repeat the string "abc" 10 times with UPPER-lower conversion, I would execute this statement:

stg.dup ('abc', stg.ul, 10);

By referencing the stg.ul constant, I can verify at compile time that I am using a valid action code for case conversion.

Notice that I have placed the dup function within a very generic string package. I do this to anticipate future requirements for string processing. By creating this package, I have established a repository in which I can place other, related functions and procedures as I think of them. All will be called with the "stg" prefix, indicating that they are oriented to string processing.

Example 3.11: A Duplicate String Package

CREATE OR REPLACE PACKAGE stg
IS
   lu CONSTANT VARCHAR2(1) := 'A';
   ul CONSTANT VARCHAR2(1) := 'B';
   n  CONSTANT VARCHAR2(1) := 'X';

   FUNCTION dup 
      (stg_in IN VARCHAR2, 
       action_in IN VARCHAR2 := n,
       num_in IN INTEGER := 1)
   RETURN VARCHAR2;
END stg;
/        
CREATE OR REPLACE PACKAGE BODY stg
IS
   FUNCTION dup
      (string_in IN VARCHAR2, 
       action_in IN VARCHAR2 DEFAULT n,
       num_in IN INTEGER := 1)
   RETURN VARCHAR2
   IS
      v_action VARCHAR2(10) := UPPER (action_in);  
      initval VARCHAR2(32767);
      nextval VARCHAR2(32767); 
      v_retval VARCHAR2(32767);
      
   BEGIN
      assert
         (v_action IN (lu, ul, n),
          'Please use the package constants: ul, lu or n');
      assert
         (num_in >= 0, 'Duplication count must be at least 0.');

      IF v_action = ul
      THEN
         initval := UPPER (string_in);   
         nextval := LOWER (string_in);

      ELSIF v_action = lu
      THEN
         initval := LOWER (string_in);      
         nextval := UPPER (string_in);

      ELSE
         initval := string_in;      
         nextval := string_in;
      END IF; 

      v_retval := 
         RPAD (initval, LENGTH (string_in) * (num_in+1), nextval);

      RETURN v_retval;         
   END dup;
END stg;
/


3.11 Don't Forget Backward Compatibility		3.13 Glancing Backward, Looking Upward