Advanced Oracle PL/SQL Programming with Packages

Chapter 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 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.