#

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

## 9.3 Retrieving Message Text

The `text` function hides all the logical complexities involved in locating the correct message text and information about physical storage of text. You simply ask for the message and `PLVmsg.text` returns the information. That message may have come from SQLERRM or from the PL/SQL table. Your application doesn't have to address or be aware of these details. Here is the header for the `text` function (the full algorithm is shown in Example 9.1):

>     FUNCTION text (num_in IN INTEGER := SQLCODE) RETURN VARCHAR2;

You pass in a message number to retrieve the text for that message. If, on the other hand you do not provide a number, `PLVmsg.text` uses SQLCODE.

The following call to `PLVmsg.text` is, thus, roughly equivalent to displaying SQLERRM:

>     p.l (PLVmsg.text);

I say "roughly" because with PLVmsg you can also override the default Oracle message and provide your own text. This process is explained below.

#### Example 9.1: Algorithm for Choosing Message Text

>     FUNCTION text (num_in IN INTEGER := SQLCODE)
>           RETURN VARCHAR2
>     IS
>        msg VARCHAR2(2000);
>     BEGIN
>        IF (num_in
>              BETWEEN c_min_user_code AND c_max_user_code) OR
>           (restricting AND NOT oracle_errnum (num_in)) OR
>           NOT restricting
>        THEN
>           BEGIN
>              msg := msgtxt_table (num_in);
>           EXCEPTION
>              WHEN OTHERS
>              THEN
>                 IF oracle_errnum (num_in)
>                 THEN
>                    msg := SQLERRM (num_in);
>                 ELSE
>                    msg := 'No message for error code.';
>                 END IF;
>           END;
>        ELSE
>           msg := SQLERRM (num_in);
>        END IF;

>        RETURN msg;
>     EXCEPTION
>        WHEN OTHERS
>        THEN
>           RETURN NULL;
>     END;

### 9.3.1 Substituting Oracle Messages

The following call to `add_text` is intended to override the default Oracle message for several rollback segment-related errors:

>     FOR err_ind IN -1550 .. -1559
>     LOOP
>        PLVmsg.add_text
>        (err_ind, 'Database failure; contact SysOp at x1212');
>     END LOOP;

9.2 Storing Message Text |  | 9.4 The Restriction Toggle

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