Export and Import Oracle Integration Cloud Service Components

How to move Integration from one ICS instance to another ? This is general question and can come in mind of every single person who is working with Oracle ICS.

Oracle ICS provides the options to export and import Integration and Lookup to share between Oracle Integration Cloud environments.

Below are the topics covered in this blog:

• Exporting an Integration

• Importing an Integration

• Exporting a Lookup

• Importing a Lookup

Exporting an Integration

Integration can be exported as a IAR file that can be use in other Oracle ICS instance or import the integration into Oracle Jdeveloper to perform an advance XSLT task(for example: variable creation or using template) that can't be used in the ICS mapper. Once the mapper editing is completed, the mapper file can then be imported back into Oracle ICS service.

To export an Integration

• Click on Integrations tile from the Oracle ICS home page
• Click on the Action menu corresponding to the Integration and click on the Export button

• Integration will be saved automatically at default download location

The file is saved with a name that consists of the identifier plus the version number, and an iar extension.

Importing an Integration

Import the integrations that were previously exported as a IAR file from Oracle Integration Cloud Service.

To import an Integration

• Click on Integrations tile from the Oracle ICS home page
• Click on Import button

• In the Import Integration File dialog, click Browse to navigate to and select the file to import.
• Click Import button

If an integration already exists with the same identifier and version, confirm whether to overwrite the existing integration.

Exporting an Lookup

Once we create a lookup, we can export that lookup for use in other Oracle Integration Cloud Service environments.

To export a lookup

• Click on Lookups tile from the Oracle ICS home page
• Select the Actions menu to the right in the lookup’s row, then select Export to CSV button

• Lookup will be saved automatically at default download location

The file is saved as a CSV file with the same name as the lookup.

Importing an Lookup

Have written a separate blog that shows how to import lookup.

Reading file from remote server using SFTP connection

Below is the program that shows how to read file from SFTP server using java

JSch API is widely used for connecting a SFTP using Java.

Download JScp library from here

Below is the program to read file from SFTP using JSch library.

package com.test.ankur;

import com.jcraft.jsch.Channel;
import com.jcraft.jsch.ChannelSftp;
import com.jcraft.jsch.JSch;
import com.jcraft.jsch.JSchException;
import com.jcraft.jsch.Session;
import com.jcraft.jsch.SftpException;

import java.io.BufferedReader;
import java.io.IOException;
import java.io.InputStream;
import java.io.InputStreamReader;

public class TestJSch {
    public static void main(String args[]) throws IOException {
        JSch jsch = new JSch();
        Session session = null;
        try {
            session = jsch.getSession("user", "127.0.0.1", 22);
            session.setConfig("StrictHostKeyChecking", "no");
            session.setPassword("password");
            session.connect();

            Channel channel = session.openChannel("sftp");
            channel.connect();
            ChannelSftp sftpChannel = (ChannelSftp) channel;

            InputStream stream = sftpChannel.get("/usr/home/testfile.txt");
            try {
                BufferedReader br = new BufferedReader(new InputStreamReader(stream));
                String line;
                while ((line = br.readLine()) != null) {
                    System.out.println(line);
                }

            } catch (IOException io) {
                System.out.println("Exception occurred during reading file from SFTP server due to " + io.getMessage());
                io.getMessage();

            } catch (Exception e) {
                System.out.println("Exception occurred during reading file from SFTP server due to " + e.getMessage());
                e.getMessage();

            }

            sftpChannel.exit();
            session.disconnect();
        } catch (JSchException e) {
            e.printStackTrace();
        } catch (SftpException e) {
            e.printStackTrace();
        }
    }
}

Import CSV file to create Lookup Oracle Integration Cloud Service (ICS)

In the previous two blogs, I have described the ICS Lookup and how to create Lookup.

Oracle Integration Cloud Service Lookups

User Lookup in Oracle Integration Cloud Service

Let's suppose we have one CSV file containing hundreds or thousands of records and want to create Lookup in ICS with those values. If we'll be adding all the values manually one by one it'll be very time consuming or irritating. So, ICS provides to import CSV file that will create Lookup with all those values containing in CSV file.

This CSV file must have some pre-defined template. Download the sample file from here or export the existing Lookup from ICS Lookup page as shown in the below screenshot:

Below is the description of sample file

1) DVM: This is mandatory as per the file format

2) Country_Lookup: A lookup will be created with this name

3) CountryCode, CountryName: These represent the column in Lookup. Two column will be created with name CountryCode and CountryName

4) Values: In the last, values of the column

Let's import the CSV file in ICS

• Login into ICS console
• Click on the Lookup tiles from the home page
• Click on the Import button from the upper right corner

• Select the CSV file and click Import button

• Notice that a Lookup with name Country_Lookup has been created 

• Open the Lookup and see all the values

This way importing the CSV file will save the time and create the Lookup for us with bulk values.

HTTP Basic Auth VS WS-Security username token authentication

HTTP Basic Authentication and WS-Security username token authentication both are different authentication mechanism and independent to each other.

Let's go in depth and see how both the mechanism differs

HTTP Basic Authentication 

Basic Authentication is used in HTTP where user name and password will be encoded using base64 encoding mechanism and passed with the request as a HTTP header.

For instance:

HTTP header section will have "Authorization: Basic dGVzdDp0ZXN0"  header element.

Username and Password will be encoded using base64 mechanism which is used in Authorization header.

For example:

base64(username:password) --> base64(admin:admin)

Most of the Webservice clients have option to provide basic auth header. In SOAPUI, at "Authentication" tab, we can provide username and password. If we switch to Raw format(as shown in the above image) of the request, all the HTTP headers are visible and we can see the Basic Auth header is set.

When we use Basic Auth, the username and password setting is on the HTTP headers not in the SOAP message. SOAP message goes with HTTP body.

WS-Security username token

Securing Webservices using ws-security username token authentication mechanism is a simple mechanism to secure services. It enforces user to provide UsernameToken security header in the SOAP requests.

As shown in the above image, the ws-security header is set as part of SOAP message.

If we secure a service using user name token option, (that is, ws-security username/password authentication) we should pass ws-security headers as shown in the above image.

SOAP adapter in Oracle Integration Cloud Service

Simple Object Access Protocol(SOAP) adapter in one of the powerful adapter provided by Oracle Integration Cloud Service(ICS). The SOAP adapter can consume any external SOAP API in an ICS.

Service can be passed as payload to an external SOAP endpoint by the SOAP Adapter. Any response received from the endpoint can be sent to the next action in the integration for further processing.

Capabilities of SOAP Adapter

SOAP Adapter as a trigger

• Supports only HTTPS protocol-based SOAP endpoints for accepting incoming SOAP requests.
• Supports following securities policies:
• HTTP Basic Authentication
• WS-Username token-based authentication
• Security Assertion Markup Language (SAML)

• Supports following exchange pattern:
• Synchronous request/response
• One-way request
• Asynchronous request with callback support
• Supports accessing of standard and custom SOAP/HTTP header properties present in the incoming SOAP request and making them available as part of an Oracle ICS message for any processing in subsequent actions

SOAP Adapter as a invoke

• Supports invocation of an HTTPS protocol-based external SOAP endpoint, thereby encrypting the communications using transport layer security (TLS)
• Supports invocation of HTTP protocol-based SOAP endpoints
• Allows invocation of external SOAP endpoints that are unprotected and protected using HTTP Basic Authentication and WS-Username token-based authentication.
• Supports following TLS versions:
• TLS v1
• TLS v1.1
• TLS v1.2
• Supports configuration of standard and custom SOAP/HTTP header
• Supports invocation of external SOAP endpoints that implement the following message exchange patterns: 
• Synchronous request/response
• Oneway request
• Asynchronous request with callback support

Let's see how to create a SOAP connection in Oracle Integration Cloud Service.

SOAP Connection as a trigger

• Login into the Oracle ICS
• Select Connection tile from home page and select Create button from upper right side corner
• Search SOAP and Select SOAP adapter

• Enter below information and click Create button
• Enter Name
• Identifier would be automatically picked up based on name entered however we can edit it.
• Select Role as Trigger
• Enter Description

• Click on Configure Connectivity button, enter following information and click on OK button:
• WSDL URL: Enter WSDL URL or select WSDL from local computer by selecting the Upload File checkbox
• Suppress insertion of timestamp into request(Optional)

• Click on Configure Security button and choose any one of the following:
• Basic Authentication: Basic authentication is used in HTTP where user name and password will be encoded and passed with the request as a HTTP header
• Username Password Token: Secure webservices using WS-Security username/password authentication mechanism that is a simple mechanism to secure services. It enforces user to provide Username Token security header in the SOAP requests.
• No Security Policy: If don't want to secure your service.
• Security Assertion Markup Language(SAML)

See difference between Basic Authentication and User Name Password Token policy here

Let's select Basic Authentication and move forward

• Select Test button from upper right corner. If all the details are correct then we will see the connection is successful and progress bar will reach to 100%

• Select Save & Close button from upper right corner

SOAP Connection as a invoke

• Login into the Oracle ICS
• Select Connection tile from home page and select Create button from upper right side corner
• Search SOAP and Select SOAP adapter
• Enter below information and click Create button
• Enter Name
• Identifier would be automatically picked up based on name entered however we can edit it.
• Select Role as Invoke
• Enter Description

• Click on Configure Connectivity button and enter following information
• WSDL URL: Enter WSDL URL
• Select TLS version(Optional)
• Suppress insertion of timestamp into request(Optional)
• Ignore timestamp in the response message(Optional)
• Enable two way SSL for outbound connection(Optional)
• Identify keystore alias name(Optional)

• Click on Configure Security button and choose any one of the following depending on how external SOAP service is secured
• Basic Authentication
• Username Password Token
• No Security Policy:If external service is not secured

As external service is secured via Username Password Token policy so select the Username Password Token and enter User Name and Password to authenticate the service

• If the external service is deployed on premise environment then select Agent. In my case service is on cloud so don't require to select Agent.
• Select Test button from upper right corner. If all the details are correct then we will see the connection is successful and progress bar will reach to 100%

• Select Save & Close button from upper right corner

How to read file using ICS file adapter

In this blog, we are gonna to show you, how to use Oracle Integration cloud service file adapter to read a file from FTP location.

In my previous blog, I have explained how to configure file adapter in Oracle Integration Cloud Service.

Write file to FTP location

After this blog, we have learned how to use file adapter to read CSV file from FTP location.

Use Case: Develop a scheduled process that will read a CSV file from FTP location and will insert the CSV data into database table.

This Use case requires only simple 4 steps to complete:

1. Create FTP connection
2. Create DB connection
3. Create a Scheduled Integration
4. Activate & Run the Integration

Let's go step by step:

1. Create FTP connection: FTP connection will work as an Invoke to read data from CSV file. In one of my blog, I have already showcased how to create FTP connection. Please check it out before moving forward.
2. Create DB connection: DB connection will work as a Target point. In one of my blog, I have showcased how to create DB connection. Please check it out before moving forward.
3. Create a Scheduled Integration

• Login into ICS console
• Click on the Integration tile from ICS home page
• Click on Create button from upper right corner
• Select Orchestration pattern from the dialog box
• Enter Below information and click on Create button
• Select Scheduled radio button
• Enter Integration Name in What do you want to call your integration text box
• An Identifier would be picked up automatically from Integration name however you can edit it
• Let the Version as it is
• Enter the description in What does this integration do box
• Leave the package box as it is

• Click Invokes from right navigation, Select TEST_FTP_Conn, drag and drop the TEST_FTP_Conn just below Scheduled activity

• Enter Name and optionally the description. Click Next button

• Enter below information and click Next button
• Select Operation: In our case select Read File
• Select a Transfer Mode: Select ASCII
• Enter Input Directory: Enter directory location from where the file would be read
• Enter File Name: Enter file name

• Select Yes radio button from Do you want to define a schema for this Endpoint and select Create a new schema from a CSV file option as shown in below screen shot and click Next button

• Enter below information and Select Next button
• Select a new Delimited File: Select the sample Test.csv file(download from here)
• Enter the Record Name: Employees
• Enter the Recordset Name: Employee
• Select the Field Delimiter: Select Comma(,) as the CSV file is comma separated
• Character Set: Select ASCII
• Optionally Enclosed By: "

• Select Done button from Summary page
• Drag & Drop For Each activity from the Actions tab just below the ReadFile activity. This For Each will be used to iterate over the CSV file records

• Enter below information from the opened popup of For Each and click Done button
• Name: Enter name of the ForEach
• Repeating Element:  Expand $ReadFile -> SyncReadFileResponse -> FileReadResponse -> Employee from left panel and Drop the Employees element in Repeating Element box
• Current Element Name: Enter EmployeeRecord

• Click Invokes from right navigation, Select Oracle Database, drag and drop the TEST_DB_Conn under ForEach

• Enter below information and Click Next
• Enter endpoint name in What do you want to call your endpoint
• Select Run a SQL Statement from What operation do you want to perform drop down

• Enter below insert query in SQL query box and Click Validate SQL query to validate the entered query and Click Next button

INSERT INTO apps.xx_employee_info(employee_id, first_name,last_name,qualification,designation) values(#employee_id,#first_name,#last_name,#qualification,#designation)

• Click Done
• Open InsertDetails mapper and map the fields from left panel(EmployeeRecord -> Employee) to right panel(InsertDetailsInput). Click Validate and Close button 

• Click on the Action menu and Tracking button

• Drop startTime to the Tracking Field

This completes our integration. Close Save and Close button

    4. Activate the Integration

• Click on Activate button

• Select Enable Tracking and Include Payload check boxes and click Activate button
• Click on Action Menu and Click on Submit Now button

As soon as we click on the Submit Now button, integration will be submitted. Now see your database tables. Whatever record exist in the CSV file, it must be inserted into database table.

This is how we can use File adapter to read the CSV file.