[Chapter 9] 9.3 Retrieving 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;