Reading Large Files (above 10MB) in Oracle Integration Cloud Service (OIC)

In today's post of Oracle Integration Cloud Service (OIC) we are going to cover the most significant design options depending on your file size, content format and delivery for files exceeding 10MB.

The integration cloud provides multiple designs and modeling options to process large files above 10 MB. For instance, the Download file options enables file loading up to 1GB in Integration Cloud local file system. This file can be received in OIC with the help of File Transfer Protocol (sFTP) server or using REST/SOAP APIs. Then it is read in segments by the stage activity.

At Conneqtion Group, we work on Oracle Integration Cloud (OIC) and plan to share our knowledge and experience with the help of technical blogs. These blogs contain the main points so that a beginner in Oracle Integration Cloud (OIC) can easily create Oracle Integration Instance and Consoles. Let us proceed with the actual problem, the solution that we propose and our approach.

The Problem

In Oracle Integration Cloud stage read activity has a limit of 10MB, using stage read activity it is not possible to read the files greater than 10 MB.

The Solution

To overcome this problem we need to use different stage activity in OIC called Read File in Segments. Using this activity we can read files greater than 10MB.

Our Approach

Now let's see the process and steps involved in using Read File in Segments.

Prerequisites:

1. Oracle Integration Cloud
2. SFTP
3. SFTP and Rest trigger connection
4. Local Integration which accepts the request payload

Let us take an example of downloading and reading the file from SFTP and calling a local integration with the data from the file.

Step 1: Place a file in SFTP location which is greater than 10MB.

Step 2:?Create a scheduled orchestration integration as shown below in the screenshot.

Step 3:?Select FTP adapter and select List Files Operation by providing the necessary details like Input directory and file name pattern.

Step 4:?Based on the List file response create a for each loop as shown below in the screenshot

Step 5:?Inside for each loop download the file from SFTP to stage location as shown below in the screenshot using FTP adapter

Step 6:?Now let's use stage Read File in Segments to read the file as shown below in the screenshot.

Map the file reference from the previous FTP download activity as shown below in the screenshot.

In the schema option select the options as shown below in the screenshot.

In format definition select the file that is placed in FTP and provide the necessary details as shown below in the screenshot.

In Summary section, we can see segment size as 200 which is fixed and it indicates data will be read and processed in the batches of 200 records.

Step 7:?Now inside the Read File in Segments section let's call a local integration with the data as shown below in the screenshot.

Map the fields from response of stage read file in segments to local integration target side.

Note: Alternatively, you can stage the data in database for further processing too. While staging, you can create Record Types and Table Types to insert the data into DB using a PL/SQL procedure.

Step 8:?By this step we have successfully created a integration which can read a file greater than 10MB.

Step 9:?Now schedule the integration to test the integration. As shown in the screenshot, integration has successfully processed the file greater than 10MB.

What is the advantage of this approach?

Using Stage Read File in Segments activity we can read the files greater than 10 MB and process the data as per requirement.

Final Thoughts

In this blog, we have tried to cover how to process large files in OIC, Download file in OIC local file system and reading file in segments. We have also included the Adapter that we used to achieve this outcome and what it offers. If you find this blog helpful and would like to discuss more, then please share your thoughts in the comments below. Alternately, you can reach out to us at [email protected].