Get XML Document from SOA Infra table

Today I'm investigating a problem in an interaction between Siebel and SOASuite. I needed to find a set of correlated messages, where BPEL expects only one message but gets 2 from Siebel.

I have a query like:

SELECT 
  dmr.message_guid,
  dmr.document_id,
  dmr.part_name,
  dmr.document_type,
  dmr.dlv_partition_date,
  xdc.document_type,
  xdc.document,
  GET_XML_DOCUMENT(xdc.document,to_clob(' ')) doc_PAYLOAD,
  xdc.document_binary_format,
  dmg.conv_id ,
  dmg.conv_type,
  dmg.properties msg_properties
FROM
  document_dlv_msg_ref dmr
  join xml_document xdc on xdc.document_id = dmr.document_id
  join dlv_message dmg on dmg.message_guid = dmr.message_guid
  where dmg.cikey  in (select cikey from cube_instance where flow_id = 4537505 or flow_id = 4537504);

To get all the messages that are related to two flows that run parallel based on the same message exchange.
The thing is that of course you want to see the contents of the message in the xml_document. This attribute is a BLOB that contains the parsed document from oracle xml classes. You need the oracle classes to serialize it to a String representation of the document. I found this nice solution from Michael Heyn.

In 12c this did not work right a way. First I had to rename the class to SOAXMLDocument, because I got a Java compilation error complaining that XMLDocument already was in use. I think it conflicts with the imported oracle.xml.parser.v2.XMLDocument class. Renaming it was the simple solution.

Another thing is that in SOA Suite 12c, the documents are apparent

set define off;
CREATE OR REPLACE AND COMPILE JAVA SOURCE NAMED "SOAXMLDocument" as
// Title:   Oracle Java Class to Decode XML_DOCUMENT.DOCUMENT Content
  // Author:  Michael Heyn, Martien van den Akker
  // Created: 2015 05 08
  // Twitter: @TheHeynComplex
  // History:
  // 2020-02-25: Added GZIP Unzip and renamed class to SOAXMLDocument
  // Import all required classes
  import oracle.xml.parser.v2.XMLDOMImplementation;
  import java.io.ByteArrayOutputStream;
  import java.io.IOException;
  import oracle.xml.binxml.BinXMLStream;
  import oracle.xml.binxml.BinXMLDecoder;
  import oracle.xml.binxml.BinXMLException;
  import oracle.xml.binxml.BinXMLProcessor;
  import oracle.xml.scalable.InfosetReader;
  import oracle.xml.parser.v2.XMLDocument;
  import oracle.xml.binxml.BinXMLProcessorFactory;
  import java.util.zip.GZIPInputStream;

  // Import required sql classes
  import java.sql.Blob;
  import java.sql.Clob;
  import java.sql.SQLException;

  public class SOAXMLDocument{

      public static Clob GetDocument(Blob docBlob, Clob tempClob){
      XMLDOMImplementation xmlDom = new XMLDOMImplementation();
      BinXMLProcessor xmlProc = BinXMLProcessorFactory.createProcessor();
      ByteArrayOutputStream byteStream;
      String xml;
      try {
              // Create a GZIP InputStream from the Blob Object
              GZIPInputStream gzipInputStream = new GZIPInputStream(docBlob.getBinaryStream());
              // Create the Binary XML Stream from the GZIP InputStream
              BinXMLStream xmlStream = xmlProc.createBinXMLStream(gzipInputStream);
              // Decode the Binary XML Stream 
              BinXMLDecoder xmlDecode = xmlStream.getDecoder();
              InfosetReader xmlReader = xmlDecode.getReader();
              XMLDocument xmlDoc = (XMLDocument) xmlDom.createDocument(xmlReader);

              // Instantiate a Byte Stream Object
              byteStream = new ByteArrayOutputStream();

              // Load the Byte Stream Object
              xmlDoc.print(byteStream);

              // Get the string value of the Byte Stream Object as UTF8
              xml = byteStream.toString("UTF8");

              // Empty the temporary SQL Clob Object
              tempClob.truncate(0);

              // Load the temporary SQL Clob Object with the xml String
              tempClob.setString(1,xml);
              return tempClob;
      } 
      catch (BinXMLException ex) {
        return null;
      }
      catch (IOException e) {
        return null;
      }
      catch (SQLException se) {
        return null;
      }
      catch (Exception e){
        return null;
      }
    }
  }
/

Also, I needed to execute set define off before it. Another thing is that in SOA Suite 12c the documents are apparently stored as GZIP object. Therefor I had to put the binaryStream from the docBlob parameter into a GZIPInputStream, and feed that to the xmlProc.createBinXMLStream().

Then create the following Function wrapper:

CREATE OR REPLACE FUNCTION GET_XML_DOCUMENT(p_blob BLOB
                                           ,p_clob CLOB) 
                    RETURN CLOB AS LANGUAGE JAVA
                      NAME 'SOAXMLDocument.GetDocument(java.sql.Blob, java.sql.Clob) return java.sql.Clob';

You can use it in a query as:

select * from (
  select xdc2.*, GET_XML_DOCUMENT(xdc2.document,to_clob(' ')) doc_PAYLOAD
  from
    (select * 
    from xml_document xdc
    where xdc.doc_partition_date > to_date('25-02-20 09:10:00', 'DD-MM-YY HH24:MI:SS') and xdc.doc_partition_date < to_date('25-02-20 09:20:00', 'DD-MM-YY HH24:MI:SS') 
    ) xdc2
)  xdc3
where xdc3.doc_payload like '%16720284%' or xdc3.doc_payload like  '%9F630D36DD24214EE053082D260AB792%'

In this example I do a scan over documents between a certain period where I filter over contents from the blob. Notice that database need to unparse the blob of every row to be able to filter on it. You should not do this over the complete table.