Introduction

The objective of this blog is to introduce to the reader all available file-based options in Oracle Integration Cloud (OIC). The blog will cover the various tools and techniques available for developers for designing file-based integration solutions

The Integration Cloud documentation does contain these in relevant sections. This blog intends to present only the file-based tools and techniques of Integration Cloud as a single point of easy reference.

We will be covering the following topics in the subsequent sections of this blog

1. Integration Cloud tools and options for File Handling
2. We will talk about mainly FTP adapter, File Adapter and their various available options for developers. A short section on SOAP and REST adapters in the context of attachments. We will also see other Integration Cloud constructs like Stage File and some Mapper functions which are vital for designing file handling solutions.

2. FAQs
3. We will answer some common integration questions around file handling

3. Use case scenarios
4. We will list out some real world use-cases and their solutions based on customer experiences. They provide useful insights and innovative uses of Integration cloud to design solutions involving File handling

Service Limits

The file size limits provided in this blog are subject to change and may not be the latest values. Oracle Integration is constantly enhanced to offer customers higher file handling capablilities.

Refer to the latest supported Service Limits from below doc links for Integration Cloud Generation 2 and Generation 3

• Oracle Integration Generation 2 - Service Limits
• Integration 3 -  Service Limits

Integration Cloud tools and options for File Handling

File Based Adapters : FTP, File, SOAP and REST adapters

File based Activities : Stage File, Mapper Functions

File Based Adapters

Integration Cloud provides these adapters that primarily help implement file-based integration flows

• FTP Adapter
• File Adapter

In addition, the SOAP and REST Connections also provide file attachment features and hence help us design flows for sending and receiving file content over SOAP and REST interfaces.

• SOAP Adapter
• REST Adapter

FTP Adapter

The FTP Adapter enables the integration of remote FTP servers (FTP/sFTP protocols) with Oracle Integration Cloud. FTP Adapter cannot be used as a trigger. Use the Scheduled orchestration and add FTP as invoke for modelling trigger type integrations. Also FTP adapter currently does not work with Connectivity agent. So the remote FTP server should be made accessible from Integration cloud. Developers can use the File adapter with connectivity agent in scenarios where it is not possible to open up the FTP server to Integration cloud. Refer here for FTP adapter documentation

The below diagram provides a ready reckoner of all options available in the FTP adapter of Integration Cloud.

We will discuss more about using these options in the subsequent sections of this blog

Large File Handling

Notice the ‘Download file’ option handles files upto size of 1 GB. This option in conjunction with the Stage ‘Segment Read’ is Integration cloud’s solution for processing large input files. This is discussed in detail in a section later in this blog. Click here to jump to large input file processing scenario

FTP Write File along with ‘append to existing file’ can be used to write iteratively and produce large file outputs.

File Adapter

The File adapter allows integrating with remote file servers for reading and writing files. Remote file can be read using a File adapter trigger. Writing to remote file can be configured using file adapter as invoke. A connectivity agent is required when integrating with file servers. Refer here for file adapter documentation

The below diagram provide a bird’s eye view of all options available in the file adapter of Integration Cloud.

SOAP adapter

In the context of file handling, SOAP adapter supports MTOM attachments and inline attachments (base64encoded string). These are often used in conjunction with File contents in integration flows.

SOAP adapter allows upto 10MB for inline attachments and upto 1GB for MTOM attachments.

Refer here for SOAP adapter documentation.

REST adapter

REST adapter supports multipart message attachments and unstructured payloads in both inbound and outbound directions upto 1GB. The attachment contents are usually used along with file processing requirements in integration flows.

Refer here for REST adapter documentation

File based activities

Integration Cloud provides these components for performing useful file based activities.

1. 1. Stage Activity       2.  File based Mapper Functions

Stage Activity:

Stage activity provides an abstracted and simple access to the Integration Cloud local file system. The absolute file paths are abstract for the developer. Developers can use logical paths, just like a real file paths, for their file operations. These are incredibly useful to perform temporary file operations before transferring files to target systems.

Stage Activity provides options to list files, read files, write files, zip and unzip files from logical directory paths form the underlying file system. There is also a specialized operation for ‘reading file in segments’ which is used to handle large files upto 1 GB. We will see more about it in later sections.

Stage File activity and its options:

Refer here for Stage activity documentation

The below diagram is a ready reckoner of all Stage options of Integration Cloud.

* In order to read an opaque file using the "Stage - Read Entire File" activity, we can provide a xsd schema file as shown below

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

Note that Integration cloud takes care of deletion and cleanup of files in the local file system . Developers need not handle these aspects of Stage files. Stage file provides an abstracted and simple access to local file system, which proves to be handy when designing a variety of integration flows.

File based Mapper Functions:

The mapper provides some specialized functions, which help with file-based operations

encodeReferenceToBase64 : The function takes a FileReference as input, reads its contents, performs base64encoding on it and returns the encoded string. This is very useful when sending file content as inline attachment over to SOAP endpoints. Also, a later section of this blog will show how this function is useful when stage file content to remote FTP server.

decodeBase64ToReference : The function takes a base64 encoded string as input. It decodes the string, writes it to a file and returns the underlying FileReference. This function is widely used to read file attachments from SOAP based endpoints and then send them over to remote FTP servers.

FAQs

Let us look at a few commonly asked questions about file handling in Integration Cloud

 'How can we read a structured file'

Files from source FTP or File server can be read into Integration Cloud as a structured file or as an opaque file. Structured files can be xml, json or a simple comma separated file (csv)

These could be a list of invoices, employee records in XML or json format which have a defined structure. Read file operation of FTP (and File) can read records from such files. The schema (or structure) can be specified by providing a schema file (xsd). Integration cloud can also generate a schema file using sample XML, JSON or CSV file, when the developer does not have the schema file.

The Read File operation reads the records from source file into memory. Subsequent mapper activities will present the schema structure allowing developer to map required values as required by the integration flow.

Figure below shows a mapping of structured elements

'How can we read images and pdf files?' - opaque files

Consider a scenario when source file from FTP is a pdf file of a contract or a zip file containing jpeg images of inventory items. These are examples of opaque files. No Schema is provided during modelling and the file is read as opaque into memory. Figure below shows configuration to read an opaque file

The output is available as a FileReference. Mapper activity allows us to map these file references to target systems. The target maybe another FTP server or a SOAP webservice which requires the image payload in base64encoded format.

Figure below shows a mapping of FTP FileReference

There is a special purpose function available in ICS, which can take a FileReference as input and convert the contents of the file into base64encoded string. This is the encodeReferenceToBase64 function we have seen earlier.

Note that SOAP connection also supports MTOM attachment which is more performant. When talking to SOAP service that accepts MTOM attachments, the FileReference can be directly assigned (or mapped) to the MTOM attachment of SOAP operation. There is no need of the base64encoding function in this case. See below figure showing SOAP adapter invoke configured with MTOM attachments option.

'When to use Segment read (aka chunked read)?'

'How do we read large files upto 1Gb?'

The ‘Read file’ approach is fine for files upto 10MB in size. However, Integration Cloud does not allow loading files larger then 10MB into memory for performance reasons. For reading larger files, developers should use the “Download file” option of FTP Connection. This allows file to be directly downloaded into ICS local file system without reading into memory.

FTP download can be used for files upto 1GB in size!

Once the file is available within Integration Cloud, then the Stage activity with operation ‘Read file in Segments’ can be used to perform chunked processing of the file contents.

Tech Tip: Degree of parallelism - The Read file in segments uses ‘parallel reads’ under the hood to enhance read performance. Occasionally there maybe use cases where the sequence of records needs to be maintained. In such cases select the ‘Process Sequentially’ to disable the parallel processing.

Recollect from Stage activity ready reckoner that Segment read is a structured read operation and hence requires the developer to provide a schema.

'Where can we decrypt a file'

Decryption can be done during 'download file' option of FTP Connection. Since ‘download file’ does not read contents into memory, the file read is effectively an opaque file. (no schema can be supplied during download file). But once the file is downloaded and decrypted, we can use the now familiar Stage read or segment read operation to read the structured data within the file!

'When can we encrypt a file'

Encryption can be performed during “Write file” option of FTP Connection. Note that both structured and opaque file can be encrypted! Choose the “enable PGP Security” as shown below

Note Note that encryption and decryption are currently available only on FTP Connection. The encryption and decryption keys should be provided in FTP connection used.

Encryption and Decryption are planned in other adapters like SOAP and Fusion HCM. Lookout the ‘whats new’ section of Integration Cloud for these new features in the future.

We will attempt to update this blog with details of these new features when they become available.

Use case Scenarios

Now let us look at a few example scenarios to see where these above mentioned “activities” can be used to design solutions. Here are some example scenarios

1. 1. A source file of inventory records to be read from FTP and each record to be updated in target system using SOAP webservice
2. 2. Accept a pdf file from source FTP and upload to a SOAP webservice as MTOM attachment
3. 3. Read a HDL file from vendor FTP and load it into Oracle Fusion HCM using UCM webservice as inline attachment
4. 4. Read a zip file containing images and upload each image to a REST service as binary payload
5. 5. Read a large file from FTP and process all records to a target database or ERP system
6. 6. Read records over multiple REST calls , aggregate in local file and transfer file to remote FTP server - Aggregate file write
7. 7. Read an encrypted file from source FTP. Decrypt the contents and process individual records
8. 8. Encrypt a clear text file from source FTP and transfer to target FTP server
9. 9. Read an encrypted zip file containing multiple files and process each file to target system

Sample solutions for above scenarios

Let us look at brief solutions to the above scenarios using the tools that we have familiarized ourselves with by now.

1. A source file of inventory records to be read from FTP and each record to be updated in target system using SOAP webservice

FTP Structured Read File

This can be done using FTP Read File with schema of inventory record specified. For-Each construct can loop through each record to map to soap message and invoke soap webservice per record. Here is how it looks.

2. Accept a pdf file from source FTP and upload to a SOAP webservice as MTOM attachment

FTP Opaque Read File – SOAP MTOM

FTP Read without specifying a schema will do the trick with opaque files. A FileReference object is available for mapping to target endpoint request.

FileReference can be mapped directly to the MTOM attachment, as shown below

3. Read a HDL file from vendor FTP and load it into Oracle Fusion HCM using UCM webservice as inline attachment

FTP Opaque Read File – SOAP inline attachment

The integration flow would look similar to Scenario.2 above.

The difference is in the mapping. The target UCM Webservice, in this case, expects an inline (base64encoded) attachment

Notice the use of the handy encodeReferenceToBase64 function during mapping values!

encodeReferenceToBase64 function converts File content to base64encoded string suitable for SOAP inline attachments

4. Read a zip file containing images and upload each image to a REST service as binary payload

FTP Download Unzip and Stage

The solution looks as above. The FTP Download file option with Unzip should be used.

Stage List activity and For-each can then be used to traverse through each image and post it to the REST service as raw payload (application/octet-stream) using the Generic Rest adapter

Stage List activity and For-each can then be used to traverse through each image and post it to the REST service as raw payload (application/octet-stream) using the Generic Rest adapter.

Same approach can be used even if passing the image file as multipart attachment.

5. Read a large file from FTP and process all records to a target database or ERP system

FTP stage Segment

In this scenario we are assuming a file size of ~500MB. So using FTP Read is not recommended, the file would need to be downloaded using FTP “download file” option. Once the file is in Integration cloud file system, Stage option of “Read entire file” cannot be used as the file is larger than 10MB

Use the Segment Read option of Stage activity

Figures below show configuring “CRUD” operations on ERP Connection and choice of Business Object

A structured mapping like below helps map the read segment records into ERP payload

6. Read records over multiple REST calls, aggregate in local file and transfer file to remote FTP server

Aggregate file write

We have seen use cases for use of Stage Read and Stage Segment read. This scenario will show the utility of Stage Write file activity

The figure below shows a possible solution. REST endpoint calls are made within the while loop. Every response record is appended to a local file on Integration cloud using Stage File Write operation with an append option.

At the end of this file aggregation, Stage List Files operation is used to obtain a FileReference to this local file. Finally FTP Write or File Write can then be used to transfer the file to remote server.

Notice the use of the mapping function to convert FileReference to binary content, since we our remote Write file uses opaque schema.

The remaining scenarios are left as exercise for the reader. Please refer to the FAQ section above.

1. 7. Read an encrypted file from source FTP. Decrypt the contents and process individual records
   1. Hint: Use FTP adapter to  'Download file with decryption' and later use Stage 'Read entire file'.
2. 8. Encrypt a clear text file from source FTP and transfer to target FTP server
   1. Hint: FTP 'Write file with encryption option enabled'.
3. 9. Read an encrypted zip file containing multiple files and process each file to target system
   1. Hint: FTP 'Download file with decryption and unzip' and further stage activities as needed.

Do feel free to post your questions or comments on this blog.

--Shreeni