It is easy to access the PeopleSoft message log in Process Monitor component, but it can be a little difficult to work with in SQL because it is stored in multiple tables.
This started when I wanted to generate a PeopleSoft log message as a single string of text, so I could investigate shared pool memory errors by searching for ORA-04031 errors. Ultimately, the string 'ORA-04031' is stored in PS_MESSAGE_LOGPARM, but I wanted to see the whole error message.
- Each process, has a request record on PSPRCSRQST, it can have many messages.
- Each message is stored in the message log table PS_MESSAGE_LOG
- The text of each message is stored in the message catalogue table PSMSGCATDEFN. It can have up to 9 substitution strings (%1, %2, etc).
- These correspond to up to 9 parameters stored on PS_MESSAGE_LOGPARM that are substituted into the message string.
I assemble the message text in a PL/SQL function exactly as PeopleTools programs do, substituting the variables in the message string from the message catalogue with the parameter values. The PL/SQL function is put into the SQL query as a common table expression so that I don't have to create a function or package in the database. The function returns the full message text in a CLOB, thus I can then easily manipulate the message string in SQL.
In this case, I wrote a SQL query to search for ORA-04031 (see psmsglogora4031.sql on Github), but the same PL/SQL function can be used in various queries.
It can be slow to search the generated message string. It can be faster to search PS_MESSAGE_LOGPARM directly.
WITH FUNCTION psmsgtext(p_process_instance INTEGER, p_message_seq INTEGER) RETURN CLOB IS
l_message_log ps_message_log%ROWTYPE;
l_message_text CLOB;
BEGIN
SELECT *
INTO l_message_log
FROM ps_message_log
WHERE process_instance = p_process_instance
AND message_seq = p_message_seq;
SELECT message_text
INTO l_message_text
FROM psmsgcatdefn
WHERE message_set_nbr = l_message_log.message_set_nbr
AND message_nbr = l_message_log.message_nbr;
--dbms_output.put_line(l_message_text);
FOR i IN (
SELECT *
FROM ps_message_logparm
WHERE process_instance = p_process_instance
AND message_seq = p_message_seq
ORDER BY parm_seq
) LOOP
--dbms_output.put_line(i.message_parm);
l_message_text := REPLACE(l_message_text,'%'||i.parm_seq,i.message_parm);
END LOOP;
--and tidy up the unused replacements at the end
RETURN REGEXP_REPLACE(l_message_text,'%[1-9]','');
END;
x as (
select r.prcstype, r.prcsname, r.oprid, r.runcntlid
, l.*, psmsgtext(l.process_instance, l.message_seq) message_text
from ps_message_log l
LEFT OUTER JOIN psprcsrqst r ON r.prcsinstance = l.process_instance
WHERE …
)
select *
from x
ORDER BY dttm_stamp_sec
/
Process Process Operator Process Msg Msg Msg
Type Name ID Run Control Instance Seq JOBID PROGRAM_NAME Set# Msg# Sev DTTM_STAMP_SEC
-------------------- --------------- ---------- ---------------------- ---------- ---- --------------- --------------- ----- ----- ----- ----------------------------
MESSAGE_TEXT
------------------------------------------------------------------------------------------------------------------------------------------------------
nVision-ReportBook RPTBOOK VP1 NVS_XXXXXXX_99 1234567 1 PRCS SCHDL psprschd 65 70 0 01/04/2023 20.37.21
Process Request shows status of 'INITIATED' or 'PROCESSING' but no longer running
nVision-ReportBook RPTBOOK VP1 NVS_XXXXXXX_99 1234567 2 PRCS SCHDL psprschd 65 73 0 01/04/2023 20.37.23
PSNT1 failed to post files to the report repository. Server scheduled to try again on 2023-04-01-20.37.41.239539. See log
...
Posts
