Previous Table of Contents Next

If we put all our logic inside the Build_Error() procedure, we won’t need a package. However, it sounds like there will be some fairly complex code in this procedure, so it would be better to break the procedure down into some smaller components. We can determine what these components are by specifying parameters for the procedure.

The Build_Error() procedure needs to accept the following parameters:

  The name of the module that owns the calling procedure or function. This is simply a string, although for the sake of simplicity, we’re going to require that it be one of a small set of module names for the system.
  The name of the calling procedure or function. This parameter needs to be accepted just in case there is a problem and we need to debug the interface to the error-generating code.
  The error number for the message that should be generated. This is an integer value.
  The data that should be included in the message. This can be handled either by passing a delimited string to the procedure or by passing a PL/SQL table to the procedure. Either approach will work, so we’ll use the delimited string. Using a PL/SQL table would require passing the number of elements in the table as a parameter to several different subroutines in the package.
  A flag indicating whether the message is being generated for display to the user. This will be either a Y or an N.

Because we’re using a delimited string to store the data that should be contained in the message, it would be a good idea to have a routine within the package that gets the next portion of data from the string. Let’s call this routine Next_Word().

We’ll decide on other subroutines as we go along. A lot of this will be decided once we’ve drafted some pseudocode for the procedure.


So far, we’ve identified two subroutines to be included in the package: the function Next_Word() and the procedure Build_Error(). We also know that the Build_Error() procedure must be public and therefore must be declared within the package spec.

Listing 6.10 shows the logic for the Next_Word() function.

Listing 6.10 Logic for the function Next_Word().

find the location of the specified delimiter in the string;

if the specified delimiter isn't the first delimiter then
  find the previous delimiter's position
end if;

return the portion of the string between the two delimiters;

This pseudocode looks a little odd and will look complicated when translated into PL/SQL. It would be better if Next_Word() were a procedure and used OUT and IN OUT parameters to return multiple values. Let’s rewrite the pseudocode to accommodate this change. The revised logic is shown in Listing 6.11.

Listing 6.11 Revised pseudocode for the Next_Word() function.

find the first delimiter in the data string;

extract the portion of the data string prior to the delimiter;

shorten the data string;

return the modified data string and the next data portion to
  the calling routine;

Listing 6.12 shows the logic for the Build_Error() procedure.

Listing 6.12 Logic for the Build_Error() procedure.

if the specified module or error number doesn't exist then
  write an "unknown module" error message;
  exit the procedure normally;
end if;

get the first portion of the error message for the module;

while there is more data to be included in the message loop
   stick the next piece of data onto the string;
   stick the next portion of the message onto the string;
end loop;

write the message to the system errors table;

if any unhandled errors occur during processing then
   place a recursive call to this procedure to log an "unhandled
     error" error message (watch for infinite loops!);
end if;

Looking at the highlighted lines of this pseudocode, we can identify at least one other module that might be usable within the package—a function that retrieves the next portion of the error message, as needed. Let’s call this function Next_String(). The pseudocode for the Next_String() function is shown in Listing 6.13.

Listing 6.13 Logic for the Next_String() function.

query the next part of the error message from the ERROR_MESSAGES

increment the message part counter;

return the next part of the error message;

This seems pretty straightforward. We’re now ready to code.

Previous Table of Contents Next