2

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?

User1974
  • 276
  • 1
  • 17
  • 63

2 Answers2

2

It sounds like it's not possible because the value is compressed:

Starting in Maximo 7.6, the messages written by the Message Tracking application are stored in the database. They are no longer written as xml files as in previous versions.

Customers have asked how to search and view MSGDATA data from the MAXINTMSGTRK table.

It is not possible to search or retrieve the data in the maxintmsgtrk table in 7.6.using SQL. The BLOB field is stored compressed.

MIF 7.6 Message tracking changes

User1974
  • 276
  • 1
  • 17
  • 63
2

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)
Andrey Batyrenko
  • 34
  • 2
  • I found another way to convert Byte Array to String. It's more simple: `from java.lang import String strData = String(byteArray) ` – Andrey Batyrenko Dec 16 '19 at 18:00
  • You can create a new custom table "ZZmaxintmsgtr" with MSGDATA_CLOB column. Create a new relationship 1 to 1 from "maxintmsgtrk" table to "ZZmaxintmsgtrk" table (using unique id column value). Use Escalation or Cron task + Automation script to uncompress data from "maxintmsgtrk.MSGDATA" and put uncompressed data into "ZZmaxintmsgtrk.MSGDATA_CLOB". As result, you can use SQL query against "ZZmaxintmsgtrk.MSGDATA_CLOB". – Andrey Batyrenko Apr 04 '21 at 04:49