Oracle ICS Stage File Action To Combine Results Inside a Loop

With Oracle Integration Cloud Service (ICS), Enterprise grade Cloud Integrations can be developed in less time compared to the usage of traditional middleware tools.

This blog post addresses on how to combine the results of multiple calls to return the result set through ICS.

Use Case

Requirement is to invoke a service hosted at a SaaS application and return the result set.  But each record in the result set from the SaaS service contains Ids. Due to this, the result set has to be iterated through to make further calls to SaaS services based on Ids to retrieve the relevant details and each record is populated with the results from multiple calls.

Solution

ICS Stage Action within the Orchestration pattern can be used to combine the results inside the For-Each loop.

To implement the concept, a simple Integration is developed in ICS with the following high level steps.

• Define REST trigger in ICS orchestration.  GET operation without any parameters and response is defined as an array containing few fields.
• Invoke an external REST service which returns an array containing all required data except one of the data fields (user details) is returned as an Id (user_id), hence a separate call is required for each record to retrieve the user details.
• Define For-Each to iterate through the records received in the previous step.
• Invoke an external REST service to get the user details based on the ID passed in the request as a parameter i.e., GET based on id.
• Use Stage File Action to write the required output data to a file within ICS.  Use Write operation and select File Append option.  CSV file format is defined for the record.
• Use Stage File Action to Read Entire File from ICS.  Same CSV file format is defined for the contents of the file.
• Add Map Action to map the Array response from Stage File Action in “4” to ICS Response.  Due to this, all records are mapped to the output array.

Implementation

Two REST connections are defined - one for Trigger and another for Invoke with base URL set to the public sample site https://jsonplaceholder.typicode.com/

The following screenshots describe the implementation which are self-explanatory.

REST Trigger

REST Invoke

ICS Flow

Trigger Configuration

Invoke to Retrieve List of Records from external REST service

For-Each Logic

Map and Invoke REST service to retrieve User Details information

Use Stage File Action to Write the complete record to a file in ICS

Outside the For-Each loop, use Stage File Action to read the complete file from ICS and map it to the response of the Integration

Please note that this pattern is not suitable for real-time integration as the total time to process the request is dependent on the number of records returned by the external service.  Real-time integration is shown in this blog post for the sake of explaining the use of Stage File action to combine the results within a for-each loop.  In real situation, this pattern would be configured as a scheduled integration where the final result is saved to a file and FTP-ed to a remote server.

Please note that the above screenshots were taken using ICS version 17.3.3