

Advanced Oracle PL/SQL Programming with Packages

Advanced Oracle PL/SQL Programming with PackagesSearch this book
Previous: 8.7 Implementing PLVtab.display Chapter 9 Next: 9.2 Storing Message Text

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

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 functio n.

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:




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.

Previous: 8.7 Implementing PLVtab.display Advanced Oracle PL/SQL Programming with Packages Next: 9.2 Storing Message Text
8.7 Implementing PLVtab.display Book Index 9.2 Storing Message Text

The Oracle Library Navigation

