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
- Specify your search string as a regular expression for the Oracle
REGEXP_INSTRfunction.
/*
Search Message Catalog for a Text String
http://www.peoplesoftwiki.com/books/database/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;
/
No Comments