start page | rating of books | rating of authors | reviews | copyrights

Advanced Oracle PL/SQL Programming with Packages

Advanced Oracle PL/SQL Programming with PackagesSearch this book
Previous: 9.2 Storing Message Text Chapter 9
PLVmsg: Single-Sourcing PL/SQL Message Text
Next: 9.4 The Restriction Toggle
 

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;


Previous: 9.2 Storing Message Text Advanced Oracle PL/SQL Programming with Packages Next: 9.4 The Restriction Toggle
9.2 Storing Message Text Book Index 9.4 The Restriction Toggle

The Oracle Library Navigation

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

Library Home Oracle PL/SQL Programming, 2nd. Ed. Guide to Oracle 8i Features Oracle Built-in Packages Advanced PL/SQL Programming with Packages Oracle Web Applications Oracle PL/SQL Language Pocket Reference Oracle PL/SQL Built-ins Pocket Reference