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, "Retrieving Message Text" , 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 and transfers them to the PL/SQL table. Recall that the PLVmsg msgtxt_table is not filled sequentially; the rows defined in the table are determined by the contents of the code column in the specified table.

To make the package as flexible as possible, PLVmsg relies on DBMS_SQL so that you can use whatever database table fits (or already exists) in your schema. When you call load_from_dbms , you tell it the name of the table and its columns, as well as an optional WHERE clause. The PLVmsg program then constructs the SQL necessary to grab the text data. The only requirement of the table is that it has a numeric column for message numbers (used as PL/SQL table rows) and a string column for the message text.

You must, at a minimum, provide the name of the messages table. The default names of the columns are :

error_code

The error number for the message

error_text

The text of the error message

In the following call to load_from_dbms , I rely on the full set of defaults for the structure of the error table to transfer all rows from the error_messages table:

PLVmsg.load_from_dbms ('error_messages');

This request will work only if the error_messages table has columns named error_code and error_text .

In this next example, I supply customized values for all arguments:

PLVmsg.load_from_dbms    ('errtxt',     'code BETWEEN -20000 AND -20999',      'code', 'text');

My table is named errtxt and has two columns, code and text . I further request that only the text for messages with error numbers between -20,000 and -20,999 be placed in the PLVmsg PL/SQL table. This WHERE clause implies that for all other errors, my program will rely on the message returned by SQLERRM (see the next section for more details).

You might be asking yourself: why bother with PLVmsg if you already have a database table-driven architecture for such messages? There are two key advantages:

1. With PLVmsg you will be reading the message text from memory (after the initial transfer) without having to go through the SQL layer. This will improve performance, though it will also require more memory since each user of PLVmsg will have her own copy of the messages.

2. PLVmsg is very flexible, in that you can dynamically direct your program to either the PLVmsg text or the database error message.