Integration

Get Involved. Join the Conversation.

Topic

    M Sridhar
    Oracle OIC Integration with OTBI IssueAnswered
    Topic posted November 13, 2018 by M SridharSilver Medal: 2,000+ Points, tagged Cloud, Integration, Orchestration, PaaS, SOAP, Web Services 
    953 Views, 18 Comments
    Title:
    Oracle OIC Integration with OTBI Issue
    Summary:
    Oracle OIC Integration with OTBI Issue
    Content:

    Hello,

    I am consuming data of a simple BI report (csv output) in OIC, issue is all the data is coming in one line (header+data) -> after decodebase64

    like inventory_item_id,organization_id12345,101

    instead of  inventory_item_id,organization_id

                         12345,101

    I tried with XML output, when it is coming in payload after decodebase64

    <?xml version="1.0" encoding="UTF-8"?> is coming in between tags which is corrupting the payload

     

    Please let me know how to handle either of the case to get it in correct format

    Best Comment

    Mani

    Hi Sridhar,

    I am not sure if there is a one step straight forward way to do it but below 2 step process works well for me

    1. write output from BIP Report to a temp.csv or temp.xml (depending on BIP output file type) using stage file activity applying decodebase64 schema

    2. read the temp.csv or temp.xml file using stage file activity applying actual schema of the data in the temp file

    Thanks,

    Comment

     

    • Monish Munot
      If you are using run report service make use flattenXML flag and set it true. This should help you if understand the issue properly. One more flag is in the payload which would help you for csv as well.
      • M Sridhar

        I used flattenXML to true option, I am not getting UTF-8 encoding tag, but XML is coming as corrupted like below...Anything I can do..?

         

         <nsmpr2:DATA_DS_MAIN>      <nsmpr2:G_2>         <nsmpr2:G_3>&lt;ROWSET>&lt;ROW>&lt;INVENTORY_ITEM_ID>300000002042783&lt;/INVENTORY_ITEM_ID>&lt;ORGANIZATION_ID>300000001577001&lt;/ORGANIZATION_ID>&lt;PRIMARY_UOM_CODE>EA&lt;/PRIMARY_UOM_CODE>&lt;INVENTORY_ITEM_STATUS_CODE>Active&lt;/INVENTORY_ITEM_STATUS_CODE>&lt;ORGANIZATION_CODE>00A&lt;/ORGANIZATION_CODE>&lt;ITEM_NUMBER>SXYZD&lt;/ITEM_NUMBER>&lt;DESCRIPTION>SXYZD&lt;/DESCRIPTION>&lt;/ROW>&lt;ROW>&lt;INVENTORY_ITEM_ID>300000002042710&lt;/INVENTORY_ITEM_ID>&lt;ORGANIZATION_ID>300000001577001&lt;/ORGANIZATION_ID>&lt;PRIMARY_UOM_CODE>EA&lt;/PRIMARY_UOM_CODE>&lt;INVENTORY_ITEM_STATUS_CODE>Active&lt;/INVENTORY_ITEM_STATUS_CODE>&lt;ORGANIZATION_CODE>00A&lt;/ORGANIZATION_CODE>&lt;ITEM_NUMBER>SWYZC&lt;/ITEM_NUMBER>&lt;DESCRIPTION>SXYZC&lt;/DESCRIPTION>&lt;/ROW>&lt;/ROWSET></nsmpr2:G_3>      </nsmpr2:G_2>   </nsmpr2:DATA_DS_MAIN>

        • Monish Munot
          1. Did you try to read this file using stage read? Because for same logic is working and this is not corrupt data. 2. Can you share your run report payload?
          • M Sridhar

            Below is the payload for runreport...  I am getting the below error in staged read,

            Start of root element expected.Error while translating message to the mentioned format. Please make sure that the file content conforms to the schema.

            <runReport xmlns:mime="http://schemas.xmlsoap.org/wsdl/mime/" xmlns:soap12="http://schemas.xmlsoap.org/wsdl/soap12/" xmlns:wsdl="http://schemas.xmlsoap.org/wsdl/" xmlns:xml="http://www.w3.org/XML/1998/namespace" xmlns:nstrgmpr="http://xmlns.oracle.com/oxp/service/PublicReportService" xmlns:soap="http://schemas.xmlsoap.org/wsdl/soap/" xmlns="http://xmlns.oracle.com/oxp/service/PublicReportService"> <nstrgmpr:reportRequest> <nstrgmpr:flattenXML>true</nstrgmpr:flattenXML> <nstrgmpr:reportAbsolutePath>/Custom/MDM/ItemReport.xdo</nstrgmpr:reportAbsolutePath> <nstrgmpr:sizeOfDataChunkDownload>-1</nstrgmpr:sizeOfDataChunkDownload> </nstrgmpr:reportRequest></runReport>

          • M Sridhar

            Below is the payload for runreport...  I am getting the below error in staged read,

            Start of root element expected.Error while translating message to the mentioned format. Please make sure that the file content conforms to the schema.

            <runReport xmlns:mime="http://schemas.xmlsoap.org/wsdl/mime/" xmlns:soap12="http://schemas.xmlsoap.org/wsdl/soap12/" xmlns:wsdl="http://schemas.xmlsoap.org/wsdl/" xmlns:xml="http://www.w3.org/XML/1998/namespace" xmlns:nstrgmpr="http://xmlns.oracle.com/oxp/service/PublicReportService" xmlns:soap="http://schemas.xmlsoap.org/wsdl/soap/" xmlns="http://xmlns.oracle.com/oxp/service/PublicReportService"> <nstrgmpr:reportRequest> <nstrgmpr:flattenXML>true</nstrgmpr:flattenXML> <nstrgmpr:reportAbsolutePath>/Custom/MDM/ItemReport.xdo</nstrgmpr:reportAbsolutePath> <nstrgmpr:sizeOfDataChunkDownload>-1</nstrgmpr:sizeOfDataChunkDownload> </nstrgmpr:reportRequest></runReport>

          • M Sridhar

            Below is the payload for runreport...  I am getting the below error in staged read,

            Start of root element expected.Error while translating message to the mentioned format. Please make sure that the file content conforms to the schema.

            <runReport xmlns:mime="http://schemas.xmlsoap.org/wsdl/mime/" xmlns:soap12="http://schemas.xmlsoap.org/wsdl/soap12/" xmlns:wsdl="http://schemas.xmlsoap.org/wsdl/" xmlns:xml="http://www.w3.org/XML/1998/namespace" xmlns:nstrgmpr="http://xmlns.oracle.com/oxp/service/PublicReportService" xmlns:soap="http://schemas.xmlsoap.org/wsdl/soap/" xmlns="http://xmlns.oracle.com/oxp/service/PublicReportService"> <nstrgmpr:reportRequest> <nstrgmpr:flattenXML>true</nstrgmpr:flattenXML> <nstrgmpr:reportAbsolutePath>/Custom/MDM/ItemReport.xdo</nstrgmpr:reportAbsolutePath> <nstrgmpr:sizeOfDataChunkDownload>-1</nstrgmpr:sizeOfDataChunkDownload> </nstrgmpr:reportRequest></runReport>

            • Hemanth Lakkaraju

              Most common reason is - the decoded content of the XML doesn't have any namespace for the root element. But when you write the content to a file, it gets written with a namespace for the root element. So there will be a mismatch when you do a read again for the written element.

              If that is the case, you need to write the file by stripping off the element using string functions.

              What is the schema used in stage write and read? Does that have a targetNamespace? Compare the decoded content online and the stage write content to figure this out.

              • M Sridhar

                I got the issue resolved....

                1. using opaque namespace

                2. report out is in encoded-64, don't decode while writing to file

                3. write it as encoded-64

                4. while reading mention name space as encoded-64, OIC returs the actual XML.

    • Mani

      Hi Sridhar,

      I am not sure if there is a one step straight forward way to do it but below 2 step process works well for me

      1. write output from BIP Report to a temp.csv or temp.xml (depending on BIP output file type) using stage file activity applying decodebase64 schema

      2. read the temp.csv or temp.xml file using stage file activity applying actual schema of the data in the temp file

      Thanks,

      • M Sridhar

        Yes, Samething I am doing as of now...when I am writing it to temp.xml file it is corrupting data as the below data coming in between element tags

        I tried with XML output, when it is coming in payload after decodebase64

        &lt;?xml version="1.0" encoding="UTF-8"?> is coming in between tags which is corrupting the payload

         

        Can we write it to temp.csv using stage file..?

         

        Thanks,

        Sridhar

        • M Sridhar

          I am getting data like this in my XML tag...All are in one line which is not satisfying XSD in my read file(stage file) format operation....

           

          INVENTORY_ITEM_ID,ORGANIZATION_ID,PRIMARY_UOM_CODE,INVENTORY_ITEM_STATUS_CODE,ORGANIZATION_CODE,ITEM_NUMBER,DESCRIPTION,END_COL300000002042783,300000001577001,EA,Active,00A,SXYZD,SXYZD,300000002042710,300000001577001,EA,Active,00A,SXYZC,SXYZC,

        • Mani

          have you tried decoding the reportBytes manually outside the integration? is that coming out correctly? you can decode from base64  online.

          Thanks,

          • M Sridhar

            yes, it is coming correctly outside (online)...

            • Mani

              then I guess your schema to read the decoded output is incorrect as Hemanth suggested.

              Here is an easy approach creating correct schema

              1. export output from your datamodel (Edit Data Modle > View output > Export) which will give you a .xdm file and contents of the same would look something like this

              <?xml version = '1.0' encoding = 'utf-8'?>
              <!--Generated by Oracle BI Publisher -Dataengine, datamodel:_Custom_Human_Capital_Management_XYZ_DM_xdm -->
              <DATA_DS><EID>1234</EID><HIRE_DATE/>
              </DATA_DS>

              2. now use any online tool that converts xml to xsd and copy the above contents from .xdm file generated from data model as xml input

              3. use the xsd as your schema in the stage file read operation

              hope that solves your issue

              • M Sridhar

                I got the issue resolved....

                1. using opaque namespace

                2. report out is in encoded-64, don't decode while writing to file

                3. write it as encoded-64

                4. while reading mention name space as encoded-64, OIC returs the actual XML

                • Mani

                  great, so we don't need any schema other than base64 with this approach?

                  • M Sridhar

                    We need to mention schema name

                    1. While writing, mention like below

                    <schema targetNamespace="http://xmlns.oracle.com/pcbpel/adapter/opaque/" xmlns="http://www.w3.org/2001/XMLSchema">
                    <element name="opaqueElement" type="base64Binary"/>
                    </schema>

                    2. While Reading, build XSD, with starting XML tag as ROWSET -> ROW ->Your elements from BI report (I used flattenXML as true in BI report)..

                    • Stephen Bryant

                      Sridhar,

                      I am following your approach and have successfully written the encoded OTBI file to ICS.  However, I am having trouble creating XSD.  I have the elements of the OTBI Report.  What I do not know how to do is create the tags for the ROWSET-ROW and flattenXML attribute.

                      Can you provide me an example of your XSD that I can follow?

                      Thanks,

                      Steve