 |
|
Step-By-Step: Building a
Package
Oracle Tips by
Burleson
|
The primary purpose of a package is to group related procedures and
functions into a single object. With this in mind, let’s build a
package based on a common need for every system—error handling and
message generation.
Requirements
Our package needs to provide other stored PL/SQL objects with a way
to generate an error message that contains excerpts from the data
being processed. While we could certainly format the error messages
individually as problems arise, it would be easier on developers if
common code handled this process.
Error messages that are generated will be stored in the
SYSTEM_ERRORS table, as follows:
error_number NOT NULL number
error_time NOT NULL date
error_text varchar2 (200)
displayed char (1)
Our package must satisfy the following conditions:
- The code that generates error messages must be
able to insert specific pieces of data into the error message.
- The error messages that are generated will be
stored in the SYSTEM_ERRORS table and will often be displayed
to the system users. However, we also want to be able to specify
whether or not the error is displayed to the user.
- Each message should have a severity level
defined so that the proper type of message box can be displayed to
the user.
- If the problem is an Oracle error, we want to
store the data that was being processed at the time (or at least the
parameter values of the procedure or function).
- Every piece of code can potentially have
several error messages.
Based on these conditions, it seems reasonable that we’ll want to
store the error messages for each procedure and function in an
ERROR_MESSAGES table that looks something like this:
module_name NOT NULL varchar2 (30)
error_number NOT NULL number
error_part NOT NULL number
error_text NOT NULL varchar2 (30)
This table makes it possible to store a single error message in
several different pieces. Doing so will allow the code to simply pull
the pieces of the message from the table and stick a piece of data
between each portion of the message.
Severity information for messages will be stored in the
ERROR_SEVERITIES table, as follows:
module_name NOT NULL varchar2 (30)
error_number NOT NULL number
severity_level NOT NULL number
At this point, we can be certain that we’ll need at least one
procedure to be called to build a message. Let’s call this procedure
Build_Error().
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.
This is an excerpt from the book "High Performance Oracle
Database Automation" by Jonathan Ingram and Donald K.
Burleson, Series Editor. |
