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



BC Oracle tuning

Oracle training

Oracle support

Remote Oracle




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.


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.


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