Lemur zaprasza
[Chapter 9] 9.3 Retrieving Message Text Chapter 9PLVmsg: Single-Sourcing PL/SQL Message Text 9.3 Retrieving Message TextThe 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 MessagesThe 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 Text9.4 The Restriction Toggle O'Reilly & Associates. All rights reserved. |