Advanced Oracle PL/SQL Programming with Packages

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 ...