Oracle SOA Suite – Getting the payload from the Composite instance - Part2
n the previous post
Oracle SOA Suite– Getting the payload from the Composite instance - Part1 - Through Java API i explained how to get the Oracle SOA Suite composite instance payload through JAVA API. In the previous approach we are getting the full audit trail of the instance and the required input payloads are retrieved using DOM parser and XPath expressions.For the larger payloads this approach fails to retrieve the payload.
In this post i am explaining the approach to get the Binary payload data from the database by executing the SQL statement and parsing the same to string message.
In Oracle SOA 11g the input payload related to the composite instances(input to the composite and all the input payload send to the references) are stored in INSTANCE_PAYLOAD(the actual XML payload is kept in XML_DOCUMENT table) table.
By querying the INSTANCE_PAYLOAD, XML_DOCUMENT and COMPOSITE_INSTANCE we can retrieve the binary input payload for a particular composite instance.After receiving the XML payload as binary we can use the java code to transform the same to string format.
import java.io.StringWriter;
import java.sql.*;
import java.util.Hashtable;
import javax.naming.*;
import javax.xml.transform.*;
import javax.xml.transform.dom.DOMSource;
import javax.xml.transform.stream.StreamResult;
import oracle.xml.binxml.*;
import oracle.xml.parser.v2.*;
import oracle.xml.scalable.InfosetReader;
public class GetPayload {
public static Connection getConnection() throws Exception {
Context ctx = null;
Hashtable ht = new Hashtable();
ht.put(Context.INITIAL_CONTEXT_FACTORY,"weblogic.jndi.WLInitialContextFactory");
ht.put(Context.PROVIDER_URL, "t3://localhost:8000");
ctx = new InitialContext(ht);
javax.sql.DataSource ds =(javax.sql.DataSource)ctx.lookup("jdbc/SOADataSource");
return ds.getConnection();
}
public static String getPayload() {
Statement stmt = null;
Connection connection = null;
ResultSet rs = null;
String query =
"select xmldoc.document DOC " + "from xml_document xmldoc,instance_payload inspay,composite_instance cmpins " +
"where xmldoc.document_id = inspay.payload_key " +
"and inspay.instance_id = cmpins.id " +
"and inspay.instance_type='composite' " +
"and xmldoc.DOCUMENT_TYPE = 2 " +
"and inspay.instance_id = 7648669";
String payload = "";
XMLDocument doc = null;
try {
connection = getConnection();
stmt = connection.createStatement();
rs = stmt.executeQuery(query);
XMLDOMImplementation xmldomimpl = new XMLDOMImplementation();
while (rs.next()) {
BinXMLProcessor xmlprocessor =BinXMLProcessorFactory.createProcessor();
BinXMLStream xmlstream =xmlprocessor.createBinXMLStream(rs.getBlob("DOC"));
BinXMLDecoder xmldecoder = xmlstream.getDecoder();
InfosetReader xmlreader = xmldecoder.getReader();
doc = (XMLDocument)xmldomimpl.createDocument(xmlreader);
TransformerFactory tf = TransformerFactory.newInstance();
Transformer transformer;
transformer = tf.newTransformer();
transformer.setOutputProperty(OutputKeys.OMIT_XML_DECLARATION,"yes");
StringWriter writer = new StringWriter();
transformer.transform(new DOMSource(doc),new StreamResult(writer));
payload =writer.getBuffer().toString().replaceAll("<", "<").replaceAll(">",">");
}
} catch (Exception e) {
e.printStackTrace();
} finally {
try {
if (rs != null)
rs.close();
if (stmt != null)
stmt.close();
if (connection != null)
connection.close();
} catch (Exception e) {
}
}
return payload;
}
}
The same query will not work in Oracle SOA Suite 12c to retrieve the payload as some of the tables are decommissioned in Oracle SOA Suite 12c e.g. INSTANCE_PAYLOAD and COMPOSITE_INSTANCE
If the process is asynchronous then the input payload is stored into the XML_DOCUMENT, the below query can be used to retrieve the BLOB data from XML_DOCUMENT table(modify the query in java code to parse the BLOB data)
SELECT XMLDOC.DOCUMENT DOC FROM XML_DOCUMENT XMLDOC , DLV_MESSAGE DLV,DOCUMENT_DLV_MSG_REF DLVREF WHERE DLVREF.MESSAGE_GUID=DLV.MESSAGE_GUID AND MLDOC.DOCUMENT_ID=DLVREF.DOCUMENT_ID AND DLV.CIKEY=10048;
If the payload size of the synchronous process is more than the threshold the input payload is stored in AUDIT_DETAILS table as BLOB data, the following query can be used to retrieve the blob data
SELECT BIN FROM AUDIT_DETAILS WHERE CIKEY=10102
Refer the following post with more details on retrieving the data from AUDIT_DETAILS -
https://www.albinsblog.com/2014/04/getting-xml-form-auditdetails-table.html
Use the xmlparserv2.jar file from the following location $MIDDLE_HOME/oracle_common/modules/oracle.xdk_11.1.0(the xmlparserv2.jar downloaded from Google may not have some of the class files included)
