7 Replies Latest reply on Mar 20, 2013 9:28 AM by 987727

    SOA-INFRA, need to get the xml data of the instance.

    987727
      Hi all Gurus, I have some peculiar situation,


      I need to get the xml data of each instance of certain bpel processes deployed in the soa server. I know that we can get that from the XML_DOCUMENT where we will be having the blob object which will have the xml payload of the instance. But not able to join the CUBE_INSTANCE and XML_DOCUMENT.

      Requirement:

      My actual requirement is that I need to retrieve the data of each instances of certain soa process deployed in the Soa Server and then make the report of them and further process them to another process. I was able to find that we can get the complete payload from the XML_DOCUMENT but unable to relate with CUBE_INSTANCE. as there are no common columns to make a join and in the Document column of XML_DOCUMENT we can have the blob object which has the complete payload.

      Please provide me some guidance. Help appreciated.

      Thanks in advance, Venkatesh.
        • 1. Re: SOA-INFRA, need to get the xml data of the instance.
          Arik
          Hi Venkatesh,

          I think this is what you are looking for:

          Select xd.*,
          cui.*
          From xml_document xd,
          instance_payload ip,
          composite_instance ci,
          cube_instance cui
          Where xd.document_id = ip.payload_key
          And ip.instance_id = ci.id
          And ip.instance_type = 'composite'
          And ci.ecid = xxxxxxxx' --You can find it on EM. Or you can add other parameter as you wish
          And ci.ecid = cui.ecid

          Arik
          1 person found this helpful
          • 2. Re: SOA-INFRA, need to get the xml data of the instance.
            987727
            Thanks Arik for quick response and this was helpful and now I'm able to get the blob data for a particular soa process. I want to do the same thing using SOA process. Just want to know is that possible as I need to provide the data in the blob object to BAM for report generation and also to another external process. How can I make the data from blob to ordinary xml data as this blob contains the xml data only. I need to do this using the SOA process. Do I need to create a package which will take this blob as input and convert and provide me the actual xml payload. Guide me further.

            Thanks again.
            • 3. Re: SOA-INFRA, need to get the xml data of the instance.
              Arik
              You're welcome.
              Due to the reason that you have to do some data conversion from blob to clob, I think the best way is to do it within some procedure/function on the DB.
              As you said, you can send the clob as a paramter from your process and receive the required data format.
              The only thing I'm not sure of, as I didn't test it, is if you will be able to get all your required data from this field ...

              Arik
              1 person found this helpful
              • 4. Re: SOA-INFRA, need to get the xml data of the instance.
                987727
                The data that I'm receiving as of now has the unwanted symbol and the data, so I need to remove the symbols and get the data. Then I need to generate a bam report on that data. Can you guide me on how to generate the report using SOA process. however, I will be looking on the oracle docs for BAM reporting. I also want to know whether the data in the blob object of the XML_DOCUMENT table in the SOA-INFRA schema is the actual payload in the xml format or any other data will also be attached to it. As I was unable to view the data.


                Thanks Arik.
                • 5. Re: SOA-INFRA, need to get the xml data of the instance.
                  Albin
                  Also,the JAVA API's can be used to retrieve the payloads.

                  Refer the below URL for more details.
                  http://www.albinsblog.com/2011/10/oracle-soa-11g-retrieving-request.html#.UUimwDceBBE



                  Mark the posting appropriately as "helpful" or "correct answer", if your issue is solved.

                  Regards
                  Albin I
                  [http://www.albinsblog.com/]
                  • 6. Re: SOA-INFRA, need to get the xml data of the instance.
                    Arik
                    You can find here the relevant tables and their description:

                    http://docs.oracle.com/cd/E23943_01/core.1111/e10108/bpel.htm#r12c1-t12

                    Arik
                    • 7. Re: SOA-INFRA, need to get the xml data of the instance.
                      987727
                      Thanks Albin,

                      That's a good post. But my requirement is somewhat different. I have the soa process which is calling a db adapter and retrieving the xml document from the XML_DOCUMENT table for that particular process. I just want to convert the present blob object to actual payload and this must be an xml payload. Can you provide me any example or the code snippet which I can make use of?


                      Thanks in advance.