9. PLVmsg: Single-Sourcing PL/SQL Message Text

Contents:
PLVmsg Data Structures
Storing Message Text
Retrieving Message Text
The Restriction Toggle
Integrating PLVmsg with Error Handling
Implementing load_ from_dbms

The PLVmsg (PL/Vision MeSsaGe) package consolidates various kinds of message text in a single PL/SQL-based repository. Each message is associated with a number. You can then retrieve messages by number using the text function.

PLVmsg was originally designed to provide a programmatic interface to Oracle error messages and application-specific error text for error numbers in the -20,000 to -20,999 range (it is called in the PLVexc.handle program). The package is now, however, flexible enough to serve as a repository for message text of any kind.

This package allows you to:

Assign individual text messages to specified rows in the PL/SQL table (the row is equal to the message number)
Retrieve message text by number (which could be an error number or primary key)
Automatically substitute your own messages for standard Oracle error messages with the restrict toggle
Batch load message numbers and text from a database table directly into the PLVmsg PL/SQL table

This chapter shows how to use each of the different elements of the PLVmsg package.

9.1 PLVmsg Data Structures

The PL/SQL table used by PLVmsg to store message text is defined in the package body as follows:

   msgtxt_table PLVtab.vc2000_table;

A PLVmsg message can therefore have a maximum of 2,000 bytes in the text.

The rows in this PL/SQL table are not filled sequentially. The rows are the message numbers and might represent Oracle error numbers, an entity's primary key values, or anything else the user passes as the message number. As a result (for PL/SQL Releases 2.2 and earlier), the PLVmsg package must keep track of the lowest and highest number rows. These values are stored in the following private variables:

   v_min_row BINARY_INTEGER;
   v_max_row BINARY_INTEGER;

Note that a user of PLVmsg cannot make a direct reference to the msgtxt_table or the low and high row values; these data structures are hidden in the package body. I am, in this way, able to guarantee the integrity of the message text.