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;
/
Unless otherwise stated, the content of this page is licensed under Creative Commons Attribution-ShareAlike 3.0 License