Unable to generate schema for reading XML file in Oracle Integration Cloud

Problem Statement – Unable to generate Schema for Reading XML file in Oracle Integration Cloud

Error: Please select a valid XML file. Caused by oracle.tip.tools.adapters.cloud.api.CloudAdapterException. Start of root element expected.

Explanation – For an Integration requirement with JDE we received this Source sample xml file from one of our European customers. When we open this xml file and validate data and structure, we could not find anything wrong with it still OIC is throwing an error. 

After spending some more time with this file on Notepad++ we were able to figure out the reason, it was its encoding. This file’s encoding is UTF-8-BOM whereas most of the time we receive UTF-8 encoded file.



Solution –

1. Make a copy of this file and open in Notepad++ or any other editor which allows you to change file’s encoding.
2. Change it’s encoding to UTF-8 and now let’s provide this as sample file in OIC to generate schema
   
3. Using modified xml file, OIC generates schema successfully.
   
   At this stage if we complete the integration, activate and test, integration will fail for actual file but can read modified file (UTF-8 encoded file)
   
   
   Modified File read is successful
   
   
   Original file read fails.
   
   Which means that schema still cannot handle BOM encoded files. Let's move ahead and modify schema.
   
   
4. Edit Adapter endpoint configuration and modify schema manually. To do this Edit Adapter endpoint config, Click on Summary and then click on Schema. You will be now able to see NXSD schema generated for XML file.
   
   
   
   
5. Copy this schema into Notepad++ and add nxsd:parseBom="true" property in xsd:schema element. Save this file. Finally, it will look like this –
   
6. Now, navigate back to Schema tab and choose XML Schema (XSD) Document option for defining schema. Choose schema file saved in previous steps. Complete adapter configuration.
   
7. That’s it. Activate and test integration for both files. Both files will be processed successfully with this schema.
   
   
   Modified file read is successful
   
   
   Also, able to read Original file

To summarize, If you are facing an error while generating schema for a xml file and you see that there is no issue with structure or content of the file then investigate other areas and if you end up with BOM encoded file then use schema attribute nxsd:parseBom="true" to read this type of files.

Bonus tip - This property is applicable for Oracle SOA suite too and can be used where you have a situation to process BOM encoded files through SOA. 

Thank you.

Send WhatsApp notifications from Oracle Integration Cloud

In the recent times I have noticed that many businesses have implemented/integrated with IT solutions to Send and Receive orders/complaints/shipping updates on WhatsApp. So, I thought to explore on finding a simple way to achieve this from Oracle Integration Cloud. For this I found that we can leverage Twilio platform and Twilio adapter which comes as OOTB adapter with OIC. 

About Twilio - Twilio is an IT platform which provides flexible and easy ways to send communications (APIs) to multiple channels i.e., Voice/SMS/WhatsApp etc. They provide user trial accounts so that one can explore before purchasing subscriptions. More details about them is available on their website - https://www.twilio.com/

Let’s see how we can achieve this –

1. First, let’s create Free User trial account in Twilio.
• Open https://www.twilio.com/ in browser and Click ‘Signup’. In next page Click on Sign up for free.
• Provide necessary info and click on ‘Start your free Trial’ and complete setup.
• On the left side of home screen, we can see Phone Numbers/Messaging options listed down under Develop section.
  Expand Messaging -> try it out and click on Send WhatsApp Message.
• Agree and activate Sandbox.
  
  
  
2. Send WhatsApp Message from your phone to connect your WhatsApp account with Sandbox env and within few seconds you will receive confirmation. Next, you can also Try sending One-way Messaging or Two-way Messaging from Twilio platform to see how it works.
   
   
   
3. Copy Account SID and Auth token. We will need this to create connection in OIC.
   
   
   
   

Now let’s create Integration in OIC -

1. Create new Connection and Select Twilio Adapter.
2. Provide name for this connection and under security policy provide Account SID and Auth token captured in earlier stage.
3. Test and Save connection.
   You will notice that we can only select Invoke Role, which means currently we cannot trigger OIC API when an event is generated in Twilio. However, we can create rest-based API in OIC and provide this API as webhook in Twilio Sandbox Configuration page to fulfill request.
4. Now, Create new schedule type integration
5. Add Twilio adapter connection created in earlier step and configure
• Choose Send SMS or MMS in operation
• Next and Done
  
  
  
  
6. Perform Map and for the time being let’s hardcode To, Body, From fields so that we don’t have to define a source. Validate and Close map.
   
   
   Sample Values -
   To – “whatsapp:+918095831600”. As we are using sandbox, we can only provide number which is connected to it configured in previous step.
   From – “whatsapp:+14155238886” .For sandbox account only this is a valid value, 
   Body – “Whatsoever you want to send”
7. Add Business identifier, Save and Activate Integration. Finally, your Integration should look like this.
   
8. Now let’s Test integration
   
   
   
   
9. Check WhatsApp.
   
   
   
   

That’s it. Based on requirement -

• For One way communication we can develop Rest based OIC service which can be triggered from Oracle ERP, Oracle SaaS, VBCS, Oracle Apex or from other OIC service to send WhatsApp/SMS/Voice notifications
• Or, for Two way communication we can also develop Rest based API in OIC and provide it as webhook in Twilio to fulfill requests triggered by Users. 

Thank you. 😊

Update

Just a day after writing this post, I visited Decathlon to book Squash Racket service and guess what, I received repair updates and job completion update through WhatsApp.

Looping over Unique Records in Oracle Integration Cloud

Frequently we get this requirement where we have to retrieve hundreds of rows from database or by reading a file and have to send this data to downstream application. 

Catch is we can’t send all records together as downstream application will not accept it, nor we can send one record at a time as we will end up sending too many requests to downstream application, however we can group unique records and send them at once to downstream. 

In OIC, by default for-each runs for each node and we have no way to configure it to run only for unique records. Thankfully, this can be achieved by using Global Variables, Data stitch and Xslt for-each group function in Map. Let’s see how.

For this demo –
1. I am using existing OIC service [acting as downstream app] which receives and process Employee information.
2. New Service [which we will develop] will read file from FTP which contains multiple employee’s data and process each employee’s record.
3. Input File Structure used -

   

Steps -
1. Create FTP connection, Test and Save

 
2. Create New Integration of Schedule type.
3. Invoke FTP connection created in step 1 to read file. Provide sample file for OIC to create schema.

    [Assuming that input file size will be < 10mb. If the file size is more than 10 mb then we

     should use download file option and then use Read file option under Stage file action]

4. Add action ‘for each’ after File Read and provide values to configure it.

5. Add action and choose call OIC, configure and select EmployeeService

6. Map from Source to target then Validate and Save.
7. Map business Identifiers and save Integration. Finally, your integration should look like this –

 
8. Activate and Test

After testing you will notice that for-each ran 8 times whereas as per our requirement it should run only 3 times.

 

This is not what we wanted 😟. So, let’s make changes in integration to achieve our requirement.

9. Deactivate integration
10. Create Global Variable of type EmployeeService say distEmpCollection

11. Before loop add a Map action, if required choose target as EmployeeService variable, will have to write for each group-by xslt function and store only unique ID. Validate and Test.

We can see that for 3 Employee records we were able to generate 2 Employee nodes as output since Source contains only 2 unique records.

 

12. Now we will overwrite this data into global variable which we created earlier using Data stitch action.

13. Open ‘for each’ action and change root element to distEmpCollection. [See now your loop will run on this xml data which contains only distEmpCollection]

 
14. Open Map and update Mapping and Finally your Integration should look like this.

 
15. Save, activate and Test Integration.

Hope this helps. 😃