Search Message Catalog Explain Text
The following PL/SQL is my attempt to search the message catalog for a particular piece of text stated as a regular expression. It is designed for use in Oracle 10g+ databases as it makes use of Oracle's regular expressions functionality.
There are some limitations with it - in particular, the conversion from LONG to VARCHAR2 is limited to a size of 32,000 characters, but you can have more than that many characters in a message catalog entry, and if you hit such an entry, the PL/SQL will fail.
To use the PL/SQL you will need to do two things:
- Specify the message set number you want to search (*see note below).
- Specify your search string as a regular expression for the Oracle REGEXP_INSTR function. See this OTN article for further information about how to work with regular expressions in Oracle 10g+.
Note: You can modify the cursor SEARCH_MESSAGE_CATALOG SQL to suite your needs — whether you want to search for a specific message set, a range of message sets or the entire message catalog.
/* Search Message Catalog for a Text String Written by Praj Basnet, August 2009 http://www.peoplesoftwiki.com/search-message-catalog-explain-text Please run this in either sql*plus or SQLTools++. */ SET SERVEROUTPUT ON SET PAGESIZE 500; SET LINESIZE 500; DECLARE vMSG_SET PSMSGCATDEFN.MESSAGE_SET_NBR%Type; vMSG_NBR PSMSGCATDEFN.MESSAGE_NBR%Type; vMSG_TEXT PSMSGCATDEFN.MESSAGE_TEXT%Type; vMSG_SEVERITY PSMSGCATDEFN.MSG_SEVERITY%Type; vMSG_EXPLAIN varchar2(32000); vMSG_CONTEXT varchar2(32000); nPOSITION number(10); vSEARCH_REGEX varchar2(100); CURSOR SEARCH_MESSAGE_CATALOG IS SELECT MESSAGE_SET_NBR, MESSAGE_NBR, MESSAGE_TEXT, MSG_SEVERITY, DESCRLONG FROM PSMSGCATDEFN WHERE MESSAGE_SET_NBR = 99999 -- Specify message set ORDER BY MESSAGE_SET_NBR, MESSAGE_NBR; BEGIN vSEARCH_REGEX := '\sTest\s'; -- Specify your regular expression OPEN SEARCH_MESSAGE_CATALOG; LOOP FETCH SEARCH_MESSAGE_CATALOG INTO vMSG_SET, vMSG_NBR, vMSG_TEXT, vMSG_SEVERITY, vMSG_EXPLAIN; EXIT WHEN SEARCH_MESSAGE_CATALOG%NOTFOUND; nPOSITION := REGEXP_INSTR(vMSG_EXPLAIN, vSEARCH_REGEX); IF nPOSITION > 0 THEN /* Show the "text" around where the search string was found */ vMSG_CONTEXT := SUBSTR(vMSG_EXPLAIN, (nPOSITION - 25), 50); vMSG_CONTEXT := REPLACE(vMSG_CONTEXT, chr(10), ' '); vMSG_CONTEXT := REPLACE(vMSG_CONTEXT, chr(13), ' '); DBMS_OUTPUT.ENABLE(1000000); DBMS_OUTPUT.PUT_LINE(chr(13)); DBMS_OUTPUT.PUT_LINE('Message Set : ' || vMSG_SET); DBMS_OUTPUT.PUT_LINE('Message Nbr : ' || vMSG_NBR); DBMS_OUTPUT.PUT_LINE('Message Text : ' || vMSG_TEXT); DBMS_OUTPUT.PUT_LINE('Message Severity : ' || vMSG_SEVERITY); DBMS_OUTPUT.PUT_LINE('Match Position : ' || nPOSITION); DBMS_OUTPUT.PUT_LINE('Message Context : ... ' || vMSG_CONTEXT || ' ...'); DBMS_OUTPUT.PUT_LINE(chr(13)); END IF; END LOOP; DBMS_OUTPUT.PUT_LINE(chr(13)); DBMS_OUTPUT.PUT_LINE('Search Complete.'); CLOSE SEARCH_MESSAGE_CATALOG; END; /
