Search code examples
oracleblobmessage-queuetext-extractionmaximo

Maximo MAXINTMSGTRK table: How to extract text from MSGDATA column? (HUGEBLOB)

I'm attempting to extract the text from the MSGDATA column (HUGEBLOB) in the MAXINTMSGTRK table:

enter image description here

I've tried the options outlined here: How to query hugeblob data:

select
    msg.*,
    utl_raw.cast_to_varchar2(dbms_lob.substr(msgdata,1000,1)) msgdata_expanded,
    dbms_lob.substr(msgdata, 1000,1) msgdata_expanded_2
from
    maxintmsgtrk msg
where
    rownum = 1

However, the output is not text:

enter image description here

How can I extract text from MSGDATA column?

Solution

  • It's is possible to do it using Automation script, uncompress data using psdi.iface.jms.MessageUtil class.

    from psdi.iface.jms import MessageUtil
...
msgdata_blob = maxintmsgtrkMbo.getBytes("msgdata")
byteArray = MessageUtil.uncompressMessage(msgdata_blob, maxintmsgtrkMbo.getLong("msglength"))

msgdata_clob = ""
for symb1 in byteArray:
    msgdata_clob = msgdata_clob + chr(symb1)