SOA 11g - Useful SOA Infra SQL queries for Oracle SOA Production Support - Part III ( Get SOA Payload from Back end tables ( SOA Infra) )

This post will be helpful when there is a need to reprocess high number of instance due to any server outage or data source outage.

Query to retrieve the payload from back end tables:

select Document from (select Document From Xml_Document Where 1 = 1 and Document_Id in (select document_id from document_ci_ref where cikey in (Select B.Cikey From Composite_Instance A, Cube_Instance B Where A.Ecid = B.Ecid And Nvl(A.Conversation_Id,'A')  = Nvl(B.Conversation_Id,'A') And Id = #Composite Instance ID)) order by doc_partition_date) a where rownum <=1

Write a Java program that automates the reprocessing job :- 

• Connect to SOA infra database
• Retrive the payload using above query
• Convert the blob to string
• Add SOA envelope to the retrived payload
• Generate the end point url using the values from cube instance table for this particular composite
• use HTTP post to invoke the composite

Above steps is just a way to automate the reprocessing, you can use this query and automate in any other possible way as well. 

Code snippet to convert the blob to string...

                                    Blob blob = payloadDetail.getBLOB("DOCUMENT");

                                    BinXMLStream inpbin = proc.createBinXMLStream(blob);

                                    BinXMLDecoder dec = inpbin.getDecoder();

                                    InfosetReader xmlreader = dec.getReader();

                                    XMLDocument doc = (XMLDocument)domimpl.createDocument(xmlreader);

                                    //doc.print(System.out);

                                    TransformerFactory tf = TransformerFactory.newInstance();

                                    Transformer transformer;

                                    transformer = tf.newTransformer();

                                    

                                    //Convert Xml to String                       

                                    StringWriter writer = new StringWriter();

                                    transformer.transform(new DOMSource(doc), new StreamResult(writer));

                                    String output = writer.getBuffer().toString();

Use this and write your process accordingly...