search cancel

Querying the blob data in MQ*P_MSG column of MQ tables

book

Article ID: 229225

calendar_today

Updated On:

Products

CA Automic One Automation

Issue/Introduction

Is there a way to retrieve information directly from the MQ*P_MSG column in the MQ tables?  For example, if a problem occurs and traces are needed to read the information a shown in the article here: https://knowledge.broadcom.com/external/article?articleId=198905, that information is in the MQ1OWP_MSG table in the MQ1OWP_MSG table.  Attempting to use a statement like:

select utl_raw.cast_to_varchar2(dbms_lob.substr(MQOWP_MSG)) from MQ1OWP;

returns something like:

€^DEACT   01x\
q

Is there a way to decrypt this so instead of activating trace and reading it, a query can be run, pull the values and use this data for next steps?

Environment

Release : 12.3

Component :

Resolution

There is no way to get this information as it is compressed by the Automation Engine when the information is put into the MQ*_MSG column and uncompressed when it is removed.  Trying to get the hex information also doesn't get any useful information when selecting directly from the database.