Retrieving SOA payload from SOA-INFRA tables using Groovy as a scripting language

Sometimes, it is useful to programmatically retrieve payload from soa-infra tables store by SOA/BPEL persistence API.
As an administrator of the SOA platform, you can build a simple groovy script to retrieve the SOA payload and store the content in a file.
This can also be done from EM (if the audit level is set in development mode), but sometimes it is helpful to script this action using a scripting language like Groovy.

Groovy can be downloaded from here: To install it, just unzip it and adapt your environment to have it in your path.
The way of how SOA Suite 11Grx is storing the payload as a BLOB needs a little investigation, as the class oracle.sql.BLOB can not be used as-is to store the File. In order to achieve what we want, you need to use the Oracle XML API implementation as described here:

I will provide the groovy script as is just for a demo. You can then build your own SOA script to monitor your Oracle SOA Suite solution.

set MIDLEWARE_HOME=E:\middleware\mid70
groovy -cp "%MIDLEWARE_HOME%\Oracle_SOA1\inventory\Scripts\ext\jlib\ojdbc6.jar;%MIDLEWARE_HOME%\Oracle_SOA1\inventory\Scripts\ext\jlib\xmlparserv2.jar;" soa_infra_bob.groovy


import groovy.sql.Sql
import oracle.xml.parser.v2.XMLDOMImplementation
import oracle.xml.binxml.BinXMLStream
import oracle.xml.binxml.BinXMLDecoder
import oracle.xml.binxml.BinXMLProcessor
import oracle.xml.scalable.InfosetReader
import oracle.xml.parser.v2.XMLDocument
import oracle.xml.binxml.BinXMLProcessorFactory

sql = Sql.newInstance("", "DEV_SOAINFRA", "Password", "oracle.jdbc.OracleDriver")

sqlSOA = """
	select, xml_document.document_id, xml_document.document, DBMS_LOB.GETLENGTH(xml_document.document)
	from xml_document,
	where xml_document.document_id = instance_payload.payload_key
	and instance_payload.instance_id =
	and instance_payload.instance_type='composite'
	and xml_document.DOCUMENT_TYPE = 2
	and instance_payload.instance_id = 510001

rowInstance = sql.firstRow(sqlSOA)
blobInstance = (oracle.sql.BLOB)rowInstance[2]

byteStreamInstance = blobInstance.getBinaryStream()
if( byteStreamInstance == null ) {
  println "Test: Received null stream!"
} else {

   blob_size = blobInstance.length()
   println "Blob size: $blob_size"
   //byte[] byte_array_test = new byte[blob_size]
   // Write to a file - NOT working as Oracle BLOB specific
   // def fos= new FileOutputStream('c:/tmp/soa_instance_blob_output.xml')
   // fos.write(byte_array_test);
   // fos.close()

	XMLDOMImplementation domimpl = new XMLDOMImplementation();
	BinXMLProcessor proc = BinXMLProcessorFactory.createProcessor();
     BinXMLStream inpbin = proc.createBinXMLStream(blobInstance);
     BinXMLDecoder dec = inpbin.getDecoder();
     InfosetReader xmlreader = dec.getReader();
     XMLDocument doc = (XMLDocument)domimpl.createDocument(xmlreader);
   def fos= new FileOutputStream('c:/tmp/soa_instance_blob_output.xml')


and the expected result is:

C:\Users\Chenda\Documents>groovy -cp "E:\middleware\mid70\Oracle_SOA1\inventory\Scripts\ext\jlib\ojdbc6.jar;E:\middleware\mid70\Oracle_SOA1\inventory\Scripts\ext\jlib\xmlparserv2.jar;" soa_infra_bob.groovy
Blob size: 149

<bpel:process xmlns:bpel="">
         <bpel:input>gero et</bpel:input>

Of course, this is a “hack” way to do it and hopefully, Oracle has provided some higher level API to interact with your SOA Application.  This is documented here:

More to come …

About Chenda Mok

19 years of hands on experience in software design and development with emphasis on Enterprise Application Integration (EAI), Services Oriented Architecture (SOA) and Identity Management (IDM) solutions. I’m a software engineer, member of the professional service delivery team working for Salesforce. Prior to this, I worked for Oracle as Solution Architect, through SeeBeyond(06/2005), then SUN’s acquisition (04/2009). After my master’s degree in computer science in 1997; I always delivered consulting on architecture, design, implementation on integration’s field. I’m interested in architecture using EAI/SOA/IDM/BPM/Cloud technologies, software development and Java’s related technologies. I may blog about my work/activities at Salesforce, but I do not speak for my employer, past, present or future.
This entry was posted in DevTools, Groovy, SOA Suite and tagged , , . Bookmark the permalink.