Monday, April 21, 2014

Oracle SOA Suite – Getting the payload from the Composite instance - Part2

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("<", "&lt;").replaceAll(">","&gt;");      
           }
        } 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)

8 comments:

  1. excellent piece! thank you!!

    wondering if similar solution can be derived through pl/sql

    ReplyDelete
  2. thank u man, it works successfully :D

    ReplyDelete
  3. Hi Albin,

    I am getting error

    Caused by: java.lang.ClassNotFoundException: weblogic.jndi.WlInitialContextFactory

    Can you please help me to get this resolved.

    Thanks,
    Awanish

    ReplyDelete
  4. Hi I have a question, Is it possible to get the failed transactions count that are waiting for human intervention of each and every particular instances from SOAINFR, I saw in WFTASK, but I am getting only the failed Instances count, But not the failed Transactions of instances counts. Please help me .
    Thanks.

    ReplyDelete
  5. Hi I have a question, Is it possible to get the failed transactions count that are waiting for human intervention of each and every particular instances from SOAINFR, I saw in WFTASK, but I am getting only the failed Instances count, But not the failed Transactions of instances counts. Please help me .
    Thanks.

    ReplyDelete
  6. What to do, for oracle 12 c?

    ReplyDelete
  7. Please if you have details for Oracle12c. Can you share it?

    ReplyDelete