#

| Chapter 9 PLVmsg: Single-Sourcing PL/SQL Message Text |

## 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, "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.

9.1 PLVmsg Data Structures |  | 9.3 Retrieving Message Text

Copyright (c) 2000 O'Reilly & Associates. All rights reserved.