9.3 Retrieving Message Text

Chapter 9
PLVmsg: Single-Sourcing PL/SQL 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;