Advanced Oracle PL/SQL Programming with Packages

9.2. Storing Message Text

Before your programs can retrieve messages from the PLVmsg PL/SQL table, you must place these messages in the table. You can do so in one of two ways:

1. Load individual messages with calls to the add_text procedure.

2. Load sets of messages from a database table with the load_from_dbms procedure.

9.2.1. Adding a Single Message

With add_text, you add specific strings to the message table at the specified row. Here is the header for add_text:

PROCEDURE add_text (err_code_in IN INTEGER, err_text_in IN VARCHAR2);

The following statements, for example, define message text for several error numbers set aside by Oracle Corporation for application-specific use (passed with a call to the RAISE_APPLICATION_ERROR builtin):

PLVmsg.add_text (-20000, 'General error');
PLVmsg.add_text (-20100, 'No department with that number.);
PLVmsg.add_text (-20200, 'Employee too young.');

Section 9.3, later in this chapter, will show how you can extract these messages.

9.2.2. Batch Loading of Message Text

In many environments, a database table is used to store and maintain error messages, as well as other types of message text. The load_from_dbms procedure can be used to make this information available through the PLVmsg interface. The header for this procedure is:

PROCEDURE load_from_dbms
   (table_in IN VARCHAR2, 
    where_clause_in IN VARCHAR2 := NULL,
    code_col_in IN VARCHAR2 := 'error_code',
    text_col_in IN VARCHAR2 := 'error_text');

This procedure reads the rows from the specified table ...