Call (800) 766-1884 for Oracle support & training
Free Oracle Tips


Oracle Consulting Support
Oracle Upgrades
Use New Oracle Features
Oracle Replication Support
Oracle Training
Remote Oracle DBA
System Documentation
Oracle Tips
Oracle Performance
 

Free Oracle Tips


 

HTML Text

BC Oracle tuning

Oracle training

Oracle support

Remote Oracle

 

 

   
 

Translate Pseudocode into Code
Oracle Tips by Burleson
 

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.

 

             
This is an excerpt from the book "High Performance Oracle Database Automation" by Jonathan Ingram and Donald K. Burleson, Series Editor.

  
 

Oracle performance tuning software 
 
 
 
 

Oracle performance tuning book

 

 
 
 
Oracle performance Tuning 10g reference poster
 
 
 
Oracle training in Linux commands
 
Oracle training Excel
 
Oracle training & performance tuning books
 

 

   

Copyright © 1996 -  2014 by Burleson. All rights reserved.

Oracle® is the registered trademark of Oracle Corporation. 

Hit Counter