For partners that build or integrate commercially available applications and service solutions with the Oracle Cloud Platform
For partners that provide implementation or managed services around Oracle Cloud Applications
Hello,
I've successfully extracted the contents of an OTBI Report from our ERP Cloud system and presented that data in a VBCS Application Page. Thanks to those here who helped me (Mani, Hemanth, and others).
At this point, I want to do the same extract from SaaS (no problem) and post that data directly to a VBCS Business Object. The problem I am having is the OTBI Report is in XML and the REST API for the Business Object wants JSON. I tried simply mapping the XML output to the Rest API in the mapper but that does not work.
The data is about 500 lines. How would I go about this? I thought the mapping automatically took care of this.
I am able to successfully Post to the Business Object in POSTMAN in batch mode but cannot replicate this in OIC.
Any help is greatly appreciated.
Thanks,
Steve
The problem I am having is the OTBI Report is in XML and the REST API for the Business Object wants JSON. I tried simply mapping the XML output to the Rest API in the mapper but that does not work.
The above screenshot tells the mapping seems to be between a Stage Read (possibly CSV) and REST request (probably JSON)
I am able to successfully Post to the Business Object in POSTMAN in batch mode but cannot replicate this in OIC.
What is the actual problem in this step? Is there an error from VBCS?
Hemanth,
I've decided to take a step back and simplify my approach.
Here is where I am.
I can send a single record to the Business Object by hard-coding the values in the mapper.
The problem I run into is when I try to do a Batch Post to the Business Object. The correct content type is included in the headers. So I created a smaller data set which is a JSON file containing two records (batchPayloadTest.txt). I generated a schema by converting JSON to XML and the XML to XSD (batchXMLSchema.txt). The REST Service Request is based on this JSON File (RestServiceRequestJSON.txt)
I continue to get this error:
<Line 1, Column 1>: XML-20108: (Fatal Error) Start of root element expected.Error while translating message to the mentioned format. Please make sure that the file content conforms to the schema.
I am not sure how to get this to work.
Attaching the files ...
Can you attach the logs with error? looks like id field is mandatory and not mapped and since probably it is an integer (by naming standards) null value cannot be accepted causing the error.
Hemanth,
Here are the logs along with a screenshot of the latest mapper. I modified the OTBI Report to pull only two records to keep the dataset small for now. I also generated the id to included in the payload.
I am getting an "Invalid Operation for the Specified Resources" but this works in POSTMAN so can you check the mapper to make sure it looks okay.
Thanks for your help.
Steve
Can you attach the postman screenshot to compare the payload? I'm seeing - "Invalid operation BATCH for the specified resource." error in the logs. This generally occurs for a REST call when path value is incorrect in case of a BATCH operation.
Thanks Hemanth.
Attached is POSTMAN Payload and Response for Batch.
Steve
From the screenshot the endpoint being called is
https://XXXXX.ocp.oraclecloud.com:443/ic/builder/design/NewFederalAppsOTBIWorks/1.0/resources/data
But the rest configuration in the integration seems to be calling
https://XXXXX.ocp.oraclecloud.com:443/ic/builder/design/NewFederalAppsOTBIWorks/1.0/resources/data/glBalances
Entry from the activity stream:
[2019-07-03T10:50:56.105+00:00] [oicphx1I_server_2] [NOTIFICATION] [] [oracle.ics.trace.soa.bpel] [tid: 152] [userId: stephen.bryant@creoal.com] [ecid: 9c59eff9-2c76-4506-b5f8-c82d02a4d12f-00223da0,1:20948:99] [partition-name: DOMAIN] [tenant-name: GLOBAL] [FlowId: 0000MirTJIg5If0LzyH7iW1T4F9O0001gD] [ICS Activity Stream Logging]: [Code:LOAD_GLBALANCES_TWORECORDS][Version:01.00.0000][Instance:22000135][Operation:update_glBalancesBO][Endpoint:https://XXXXX.ocp.oraclecloud.com/ic/builder/design/NewFederalAppsOTBIWorks/1.0/resources/data/glBalances][Endpoint Invocation Start(UTC):2019-07-03T10:50:55.258Z][Endpoint Invocation End(UTC):2019-07-03T10:50:56.103Z][Endpoint Invocation Time(ms):845][Total Execution Time(ms):960][Http Status Code:400]: Invocation of target service failed in 845 ms
Try correcting the endpoint and see if it works!
Hemanth,
I updated the REST Connection as follows:
https://integration021783-creoalconsulting.integration.ocp.oraclecloud.com/ic/builder/design/NewFederalAppsOTBIWorks/1.0/resources/data/glBalances
I am not getting this error. This error happens if I do not define the URI in the REST Stage.
However, I get a 404 error when I do define the URI in the REST Stage. I will continue to play and I am attaching the logs.
: Fault Details : <ns2:APIInvocationError xmlns:ns2="http://xmlns.oracle.com/cloud/generic/rest/fault/REST/update_glBalancesBO"><ns2:type/><ns2:title/><ns2:detail/><ns2:errorCode/><ns2:errorDetails><ns2:type>http://www.w3.org/Protocols/rfc2616/rfc2616-sec10.html#sec10.5.1</ns2:type><ns2:title>Internal Server Error</ns2:title><ns2:errorCode>500</ns2:errorCode><ns2:errorPath><![CDATA[POST https://integration021783-creoalconsulting.integration.ocp.oraclecloud.com/ic/builder/design/NewFederalAppsOTBIWorks/1.0/resources/data/glBalances/ returned a response status of 500 Internal Server Error]]></ns2:errorPath><ns2:instance><![CDATA[.The 500 Internal Server Error is a very general HTTP status code that means something has gone wrong on the server side, but the target service could not be more specific on what the exact problem is. You can trace the cURL representation of the request sent to the target service from the Oracle Integration Cloud server logs. Try invoking the target service using cURL. If the problem persists, contact the target service admin.]]></ns2:instance></ns2:errorDetails></ns2:APIInvocationError> :Application Error
Thanks again,
Steve
Try rest connection till /resources and define the URI in rest adapter as /data and see if it works.
Hemanth,
Still getting the 500 error. Right now, this is just one record I am trying to bring in from OTBI. If I provide the full URL whether created based on the REST Connection and REST Adapter or just by the REST Connection, I get a 500 error.
Not sure what is going on.
Steve
Here is the last file
Hemanth,
I have great news --- WE DID IT! And when I say "We", I mean you and I. Your suggestion to set the REST Connection to ".../Resources", then the REST Adapter URI to "/data" and then include the remaining in the Payload as "path":"/glBalances" worked!
Everything is fine and I thank you a thousand times and then some for all your help.
Thanks,
Steve
Hemanth,
One more question.
I am able to bulk load 36 rows. After that, the integration fails. There seems to be a limit on the Business Object REST API. I posted a question on the Visual Builder Forum for that. In the meantime, I know we can chunk read files but can we post to the rest service by chunking? or do I have to do a loop? I've done this sort of thing before loading FBDI files. Also, do you know if we can paginate a Business Object REST API?
Thanks,
Steve
You may have to use loop for posting chunked messages and yes, you should be able to paginate REST API. Just add the related query parameters to rest adapter configuration.
Hemanth,
It turned out the issue was in the data. There were null values I did not account for that were in the complete data set. That is a great lesson learned for me.
Once again, thank you for all your help and patience!
Steve
Try rest connection till /resources and define the URI in rest adapter as /data and see if it works.