Code

Translating the pseudocode for the package into code is fairly straightforward. Listing 6.14 contains the package spec and the package body for the System_Errors package.

Listing 6.14 The package spec for the System_Errors package.

PACKAGE System_Errors

IS

   DELIMITER   CONSTANT char (1) := '^';

   PROCEDURE Build_Error (vModule      IN     varchar2,
                          vProcName    IN     varchar2,
                          iErrorNum    IN     integer,
                          vDataString  IN     varchar2,
                          vDisplayFlag IN     varchar2);

   PROCEDURE Next_Word (vDataString IN OUT varchar2,
                        vWord          OUT varchar2);

END System_Errors;

PACKAGE BODY System_Errors

-- ******************************************************************
   iNextPart    integer := 1;
-- ******************************************************************

FUNCTION Next_String (vModule   IN     varchar2,
                      iErrorNum IN     integer)

   RETURN varchar2

IS

   vNextStringPart   ERROR_MESSAGES.error_text%TYPE;

BEGIN
   SELECT error_text
   INTO   vNextStringPart
   FROM   ERROR_MESSAGES
   WHERE  module_name  = vModule
   AND    error_number = iErrorNum
   AND    error_part   = iNextPart;

   iNextPart := iNextPart + 1;

   RETURN vNextStringPart;
END Next_String;

-- ******************************************************************

PROCEDCURE Build_Error (vModule      IN     varchar2,
                        vProcName    IN     varchar2,
                        iErrorNum    IN     integer,
                        vDataString  IN     varchar2,
                        vDisplayFlag IN     varchar2)

IS

   MODULE           CONSTANT varchar2 (6)  := 'ERRORS';
   PROCEDURE        CONSTANT varchar2 (30) := 'Build_Error';
   UNKNOWN_ERROR    CONSTANT integer       := 1;
   bRecursion       boolean := FALSE;
   iSeverity        integer;
   vDataString      varchar2 (200);
   vNewMessage      varchar2 (200);
   vNextPart        ERROR_MESSAGES.error_text%TYPE;
   vNextWord        varchar2 (30);

   xRECURSION_ERROR EXCEPTION;

BEGIN
   IF NOT bRecursion THEN
      bRecursion := FALSE;
   END IF;

   vDataWords  := vDataString;
   vNewMessage := NULL;
   iNextPart   := 1;

   --
   -- If the module that was specified doesn't exist or the error for
   -- the module can't be found, write an "UNKNOWN ERROR" message
   -- here. This is combined with the retrieval of the severity_level
   -- for the message.
   --
   BEGIN
      SELECT severity_level
      INTO   iSeverity
      FROM   ERROR_SEVERITIES
      WHERE  module_name  = vModule
      AND    error_number = iErrorNum;

   EXCEPTION
      WHEN NO_DATA_FOUND THEN
           IF NOT bRecursion THEN
              bRecursion := TRUE;
              Build_Error (vModule      => MODULE,
                           vProcName    => PROCEDURE,
                           iErrorNum    => UNKNOWN_ERROR,
                           vDataString  => vModule   || DELIMITER ||
                                           iErrorNum || DELIMITER ||
                                           vProcName || DELIMITER,
                           vDisplayFlag => 'Y');

           ELSE
              RAISE xRECURSION_ERROR;
           END IF;
   END;

   --
   -- Get the first part of the error message from the ERROR_MESSAGES
   -- table.
   --
   vNextPart := Next_String (vModule   => vModule,
                             iErrorNum => iErrorNum);
   vNewMessage := vNewMessage || vNextPart;

   WHILE (instr (vDataString, DELIMITER) > 0) LOOP
      --
      -- Get the next piece of data from the string.
      --
      Next_Word (vDataString => vDataWords,
                 vWord       => vNextWord);

      --
      -- Put the next piece of the error message onto the
      -- new message.
      --
      vNextPart := Next_String (vModule   => vModule,
                                iErrorNum => iErrorNum);

      vNewMessage := vNewMessage || vNextWord || vNextPart;
   END LOOP;

   INSERT
   INTO   SYSTEM_ERRORS
          (error_number,
           error_time,
           error_text,
           displayed)
   VALUES (ERROR_SEQ.nextval,
           SYSDATE,
           vNewMessage,
           vDisplayFlag);
END Build_Error;

-- ******************************************************************

PROCEDURE Next_Word (vDataString IN OUT varchar2,
                     vWord          OUT varchar2)

IS

BEGIN
   iDelimiterPos := instr  (vDataString, DELIMITER);
   vWord         := substr (vDataString, 1, iDelimiterPos);
   vDataString   := substr (vDataString, (iDelimiterPos + 1));
END Next_Word;

END System_Errors;

Testing

In reality, packages are not tested; procedures and functions within the package are tested individually from the lowest point on the food chain to the highest. Testing a package requires every subroutine inside the package to be tested thoroughly. References to global variables, constants, and other constructs should be closely examined during the test.

Testing private procedures and functions is somewhat difficult. It’s often easier to test procedures and functions by making them public for testing purposes, then removing the public definition of the object from the package spec and then testing the security of the private object.

Summary

Chapter 6 has discussed the fundamentals of grouping procedures and functions using packages. At this point, you should be familiar with the creation of packages and have some insights into designing your own packages and testing the routines inside a package.