File Upload Process in Integration Cloud Services REST API

File upload is a very common aspect of each and every technology we used in our day to day projects as part of the REST service. This is very common requirement to upload a file that resides in the local computer and send it to the remote computer.

Sometimes we need to expose a REST service which should allow to upload files, process the data and store it some repository like a database, FTP etc..

In this blog, we are going to show how to use ICS REST adapter feature to upload a file and save the file as it is on some FTP location.

Below are the steps to achieve the use case:

• Create REST Adapter as Trigger
• Create FTP Adapter as Invoke
• Create Orchestration Integration
• TEST the Integration

Create REST Adapter as Trigger

There is one dedicated blog that explains, how to configure REST Adapter in Oracle ICS. Please have a look at the blog

Create FTP Adapter as Invoke

This has also been explained in another blog that shows, how to configure FTP Adapter in Oracle ICS. Please have a look at the blog

Create an Orchestration Integration

Create an Orchestrated Integration using steps:

• 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 Application event or business object 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, we can edit it
• Let the Version as it is
• Enter the description in What does this integration do input box
• Leave the package input box as it is

• Drag the TEST_REST_Conn Connection on the canvas from REST connection

• Enter below information and Click Next
• Name of the endpoint
• Relative URI(must start with /)
• Select HTTP verb as POST
• Select Configure a request payload for this endpoint checkbox- This option allows us to assign payload in the next step

• Select Accept attachments from request and Request in HTML form checkboxes then click Next and Done button

• Drag & Drop the FTP connection on the canvas

• Enter the Name in What do you want to call the endpoint input box and click Next button
• Enter below information and click Next button
• Select Operation: Select Write File
• Select a Transfer Mode: Choose ASCII
• Specify an Output directory: Enter the directory where you want to save the uploaded file
• Specify a File Name Pattern: Enter the file name

• Choose No under the Do you want to define a schema for this endpoint and click Next -> Done button

• Edit the mapper and map the below
• execute -> attachments -> attachment -> attachmentReference to ICSfile -> FileReference
• partName -> fileName

partName element would contain the file name that will be uploaded

That configuration completes the integration.

Let's test the integration using POSTMAN tool:

Check the FTP location output directory that has been given during configuring FTP adapter.

Conditional Mapping in Oracle ICS

Conditional mapping is a very common question in each tech that we use in our projects. Someone asked me how to use conditional mapping in Oracle ICS which encouraged me to write this blog.

So, let's catch the below case:

• Suppose there are two Query parameters, called A and B
• There is another third output variable called C
• Value of C should be as per below logic:
• If A & B both are not null then output should be, C  = concat(A | B)
• If A not null then output should be C  =  A
• If B not null then output should be C  =  B

So, let's look how we can achieve the preceding use case with the power of XSLT.

We'll build our expression with the help of choose function available in Oracle ICS. Below attached screenshot depicts the logic that has been built based on the above logic

Since ICS mapper is very sophisticated, so such complex mapping is very difficult to build using ICS mapper. So I would suggest, export the integration and open the XSLT file and edit the code directly with the help of JDeveloper.

Below is the part of XSLT that has been built to achieve the use case:

<nsmpr0:C xml:id="id_17">
 <xsl:choose xml:id="id_18">
 <xsl:when test="/nstrgmpr:execute/nstrgmpr:QueryParameters/nsmpr1:A!='' and /nstrgmpr:execute/nstrgmpr:QueryParameters/nsmpr1:B!=''">
     <xsl:value-of select="concat(/nstrgmpr:execute/nstrgmpr:QueryParameters/nsmpr1:A, ' | ', /nstrgmpr:execute/nstrgmpr:QueryParameters/nsmpr1:B)" />
        </xsl:when>
         <xsl:when test="/nstrgmpr:execute/nstrgmpr:QueryParameters/nsmpr1:A!=''">
            <xsl:value-of select="/nstrgmpr:execute/nstrgmpr:QueryParameters/nsmpr1:A" />
         </xsl:when>
         <xsl:when test="/nstrgmpr:execute/nstrgmpr:QueryParameters/nsmpr1:B!=''">
            <xsl:value-of select="/nstrgmpr:execute/nstrgmpr:QueryParameters/nsmpr1:B" />
         </xsl:when>
 </xsl:choose>
</nsmpr0:C>

Database polling in Oracle Integration Cloud Service

In this article, we will show how to use Oracle database polling in Oracle ICS. Oracle ICS allows very straight forward solution to poll the Oracle database in comparison to SOA.

Let's see how we can achieve database polling strategy in Oracle ICS.

• Create a database table(XX_EMPLOYEE) with below script

CREATE TABLE XX_EMPLOYEE

 (             "ID" VARCHAR2(20 BYTE),

                "NAME" VARCHAR2(200 BYTE),

                "ISNEW" VARCHAR2(10 BYTE)

 )

• Run below script to insert some values

 insert into XX_EMPLOYEE(ID,NAME,ISNEW) values(1,'Ankur Jain','NEW')

 insert into XX_EMPLOYEE(ID,NAME,ISNEW) values(2,'Nitin','NEW')

• Create a Oracle Database Connection in Oracle ICS. Have a look to this blog for Oracle DB connection. Make sure Role must be of Trigger type
• Create an Orchestrated Integration

• Drop the Oracle DB connection as a Trigger point

• Enter Name in What do you want to call your endpoint input box and click Next

• Click Import Tables

• Select the DB schema in which XX_EMPLOYEE table has been created, Enter the table name and transfer to Selected Tables. Click OK button

• We will notice the below configuration screen

• Click on Edit button of Review the polling strategy and specify polling options and enter/select below values
• Polling Strategy: Logical Delete
• Logical Delete Field: ISNEW
• Read Value: This value used to indicate the row has been processed. Enter PROCESSED
• Unread Value: Indicate the row to be process. Enter NEW
• Polling Frequency(Sec): Specify the polling frequency in seconds to process the new record

• Click Next and Done

This is the only configuration required to poll the Oracle DB. Now activate the integration and check the DB rows. We'll notice that the rows has been picked by Integration and ISNEW flag values has been set to PROCESSED

Before Activate the Integration

After Activate the Integration

Expose custom PL SQL APIs as a SOA Gateway Webservice

Integrated SOA Gateway(ISG) is an integrated component of E-Business Suite R12. ISG allows to expose EBS functionality(which includes XML gateway, Business Service Objects, Concurrent Programs, PL/SQL API's, Business Events, E Commerce Gateway, Open Interface Tables/Views etc.) as a SOAP/REST WebServices.

To expose a PL/SQL package as a service interface, it is mandatory to annotate the package with standard annotation.

Below steps to be followed to expose a PL/SQL package as a REST interface.

• PL/SQL package is annotated with the standard annotation

create or replace PACKAGE XX_SAVE_EMPLOYEE AS

/* $Header: $ */

/*#

* This custom PL/SQL package can be used to insert employees

* @rep:scope public

* @rep:product per

* @rep:displayname XX_SAVE_EMPLOYEE

* @rep:category BUSINESS_ENTITY PER_EMPLOYEE

*/

/*#

* Save records in emp table

* @param c_id varchar2

* @param name varchar2

* @rep:scope public

* @rep:lifecycle active

* @rep:displayname save_employee

*/

PROCEDURE save_employee_PRC(c_id IN VARCHAR2,

   name IN VARCHAR2);

END XX_SAVE_EMPLOYEE;

Preceded package contains a single procedure save_employee_PRC that takes two input parameters. This procedure is used to insert data(c_id and name) into the table.

Note: For PL/SQL packages, only the package spec should be annotated. Do not annotate the body.

• Create a .pls file and paste the above code in the .pls file. Move the .pls file to the EBS server via winscp or putty. In this case we will copy the file at $APPL_TOP/patch/115/sql
• Next step is to create an iLDT file (Integration Repository loader file) that we should use to upload to the Integration Repository. The file is created with a tool called Integration Repository Parser (IREP Parser), the tool will validate the file against the annotation standards.
• Run the below command to generate the iLDT file

[root@testmachine]$ $IAS_ORACLE_HOME/perl/bin/perl $FND_TOP/bin/irep_parser.pl -g -v -username=sysadminper:patch/115/sql:XX_SAVE_EMPLOYEE.pls:12.0=XX_SAVE_EMPLOYEE.pls

Note: If you are generating a new iLDT file for an already uploaded interface we need to add a higher version number then the last uploaded.

[root@testmachine]$ $IAS_ORACLE_HOME/perl/bin/perl $FND_TOP/bin/irep_parser.pl -g -v -username=sysadminper:patch/115/sql:XX_SAVE_EMPLOYEE.pls:12.1=XX_SAVE_EMPLOYEE.pls

If everything OK below would be the output of the above command

# Interface Repository Annotation Processor, 12.0.0

#

# Generating annotation output.

# Processing file 'XX_SAVE_EMPLOYEE.pls'.

# Using YAPP-based parser.

#  Found a package-level annotation for 'XX_SAVE_EMPLOYEE'.

#  Found a detail-level annotation...

# Found a procedure named 'SAVE_EMPLOYEE_PRC'.

# Done all files.

Please Note: XX_SAVE_EMPLOYEE_pls.ildt would be generated.

• Next is used to upload the generated iLDT file to integration repository. Run below command to upload the iLDT file to integration repository

[root@testmachine]$ $FND_TOP/bin/FNDLOAD <db_user>/<db_password>0 Y UPLOAD $FND_TOP/patch/115/import/wfirep.lct XX_SAVE_EMPLOYEE_pls.ildt

Note: Replace the <db_user> and <db_password> with database apps user and password.

Above command will generate a .log file. Just tail the file and see the below success output

[root@testmachine]$ tail -500f L2602439.log

+---------------------------------------------------------------------------+

Application Object Library: Version : 12.2

Copyright (c) 1998, 2013, Oracle and/or its affiliates. All rights reserved.

FNDLOAD: Generic Loader

+---------------------------------------------------------------------------+

Current system time is 15-NOV-2017 23:17:58

+---------------------------------------------------------------------------+

Uploading from the data file XX_SAVE_EMPLOYEE_pls.ildt

Altering database NLS_LANGUAGE environment to AMERICAN

Dump from LCT/LDT files

(/appl/fnd/12.0.0/patch/115/import/wfirep.lct(120.8.12020000.3), XX_SAVE_EMPLOYEE_pls.ildt) to stage tables

Dump LCT file

 /appl/fnd/12.0.0/patch/115/import/wfirep.lct(120.8.12020000.3) into FND_SEED_STAGE_CONFIG

Dump LDT file XX_SAVE_EMPLOYEE_pls.ildt into FND_SEED_STAGE_ENTITY

Dumped the batch (IREP_OBJECT PLSQL:XX_SAVE_EMPLOYEE C , PARAMS 2 0 ) into FND_SEED_STAGE_ENTITY

Upload from stage tables

+---------------------------------------------------------------------------+

Concurrent request completed successfully

Current system time is 15-NOV-2017 23:17:59

• Now create the package and package body in the database with the below script

Package

create or replace PACKAGE XX_SAVE_EMPLOYEE AS

/* $Header: $ */

/*#

* This custom PL/SQL package can be used to insert employees

* @rep:scope public

* @rep:product per

* @rep:displayname XX_SAVE_EMPLOYEE

* @rep:category BUSINESS_ENTITY PER_EMPLOYEE

*/

/*#

* Save records in emp table

* @param c_id varchar2

* @param name varchar2

* @rep:scope public

* @rep:lifecycle active

* @rep:displayname save_employee

*/

PROCEDURE save_employee_PRC(c_id IN VARCHAR2,

   name IN VARCHAR2);

END XX_SAVE_EMPLOYEE;

Package body

create or replace PACKAGE BODY XX_SAVE_EMPLOYEE AS    

   PROCEDURE save_employee_PRC(c_id IN VARCHAR2,

   name IN VARCHAR2) IS

   BEGIN

    INSERT INTO xx_test(id,name) values(c_id,name);

   END save_employee_PRC;

END XX_SAVE_EMPLOYEE;

• Now, login into EBS console and Navigate to the Integrated SOA Gateway -> Integration Repository
• Enter the display name(XX_SAVE_EMPLOYEE) in Internal Name and click on Search button

• Click on the XX_SAVE_EMPLOYEE and switch to REST Web Service tab.
• Enter Service Alias: This would be the service name
• Select save_employee Checkbox and click Deploy button

Once successfully deployed, success message will be displayed

• Switch to Grants tab, select Checkbox and click on Create Grant button

• Select Specific User from the Grantee Type drop down and enter Grantee Name to give access to specific user to execute this service. Click on Create Grant button

• Once done, success message will appear on the screen

Now the PL/SQL has been exposed as a REST interface

Click on View WADL link to see the REST endpoint.

We can test the REST service via POSTMAN tool. Below would be the endpoint of REST service. 

https://localhost/webservices/rest/EmpInsert/SAVE_EMPLOYEE_PRC/

During testing, add the BASIC authentication.

Create an Integration in ICS to expose SOAP service

In this blog, we'll demonstrate how we can use Integration Cloud Service to expose SOAP service. In one of my blog we have showcased how to expose REST services.

In this blog, we will create an Orchestrated integration that will be exposed as a SOAP service.

Use Case

• Develop a SOAP service using ICS
• Request parameter would be a user id
• Response would be username
• Service will return fault if user not found

Below are the steps to achieve the use case

1. Create DB Connection
2. Create SOAP Connection
3. Create Orchestrated Integration
4. Test SOAP service

Let's go ahead and achieve all the steps one by one

1. Create DB Connection: DB connection will work as a Target point. Please check the blog how to create DB connection.
2. Create SOAP Connection: SOAP connection will work as a Trigger point. Please check the blog how to create SOAP connection.
3. Create Orchestrated Integration: Create an Orchestration integration using below steps:

• Click on Create button from Integration page and select Orchestration pattern from the dialog box
• Enter Below information and click on Create button
• Select Application event or business object 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, we can update this
• Let the Version as it is
• Enter the description in What does this integration do box
• Leave the package box as it is

• Drag SOAP_Conn on the canvas from the SOAP connection

• Complete the SOAP wizard with some clicks Next -> Next -> Next -> Next - > Done. Once the SOAP wizard is complete, integration will look like below

• Drag Test_DB_Conn on the canvas from the DB connection just below the Map GetUserName

•  Enter the endpoint name and select Run a SQL Statement from What operation do you want to perform drop down then click Next

• Enter below query then click Validate SQL Query button. Click Next  -> Done button to complete the configuration wizard

select name from xx_employee_t where id=#userId

• Edit GetUserDetails map and map id -> userId

• Now let's check if a user exists or not. To do so, drop the Switch activity below the DB adapter and configure if action. Put the condition, username is not blank

• Drop the map activity in the if block and map, name -> name

• Drop the Fault Return activity under the otherwise. A mapper will get automatically added between the otherwise and Fault Return

• Edit the newly added mapper and you will notice that fault object that was in the WSDL will be shown automatically. Put the expression 'User doesn't exist' in the reason

Now the integration is complete. Activate the integration that will provide one WSDL once the integration is activated successfully.

Let's test the integration using SOAP UI tool.

TEST-1 : Send valid id, hit the request and notice the valid response

TEST-2 : Send invalid id, hit the request and notice fault will occur

Create Connection using external WSDL in Oracle Integration Cloud Service

Let's assume we have some custom WSDL and want to use this WSDL in Oracle Integration Cloud Service.

ICS provides SOAP adapter that allows us to create SOAP connection with the WSDL that is locally saved in our desktop.

Let's deep dive and see how to use the existing WSDL.

Create a SOAP connection with below steps:

• Click Create button from the Connection page
• Search SOAP and Select SOAP adapter

• Enter below information and click on Create button
• Name: Any meaningful name of your choice
• Identifier: Identifier would be picked up automatically from the Name however we can update this
• Role: Select Trigger and Invoke
• Description: Optional description

• Click on Configure Connectivity button
• From the opened Connection properties dialog box, select the checkbox corresponding to WSDL URL and upload the WSDL

<?xml version="1.0" encoding="UTF-8" ?>

<definitions targetNamespace="urn:EmployeeWSDL" xmlns="http://schemas.xmlsoap.org/wsdl/" xmlns:tns="urn:EmployeeWSDL"

             xmlns:soap12="http://schemas.xmlsoap.org/wsdl/soap12/" xmlns:mime="http://schemas.xmlsoap.org/wsdl/mime/"

             xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:soap="http://schemas.xmlsoap.org/wsdl/soap/"

             xmlns:weo="http://www.example.org">

  <types>

    <xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns="http://www.example.org"

            targetNamespace="http://www.example.org" elementFormDefault="qualified">

  <xsd:element name="request">

    <xsd:annotation>

      <xsd:documentation>A sample element</xsd:documentation>

    </xsd:annotation>

    <xsd:complexType>

      <xsd:sequence>

        <xsd:element name="id" type="xsd:integer"/>

      </xsd:sequence>

    </xsd:complexType>

  </xsd:element>

  <xsd:element name="response">

    <xsd:complexType>

      <xsd:sequence>

        <xsd:element name="name" type="xsd:string"/>

      </xsd:sequence>

    </xsd:complexType>

  </xsd:element>

  <xsd:element name="fault">

    <xsd:complexType>

      <xsd:sequence>

        <xsd:element name="error" type="xsd:string"/>

      </xsd:sequence>

    </xsd:complexType>

  </xsd:element>

</xsd:schema>

  </types>

  <portType name="EmpServicePort">

    <operation name="requestOperation">

      <input message="tns:EmpServicePort_request"/>

      <output message="tns:EmpServicePort_response"/>

      <fault name="EmpFault" message="tns:EmpSeriveFault"/>     

    </operation>

  </portType>

  <message name="EmpServicePort_request">

    <part name="part" element="weo:request"/>

  </message>

  <message name="EmpServicePort_response">

    <part name="part" element="weo:response"/>

  </message>

  <message name="EmpSeriveFault">

    <part name="part" element="weo:fault"/>

  </message> 

</definitions>

• Once uploaded click on OK button

• Click on Configure Security button then enter ICS credentials and click on OK button

• Click Test button from upper corner then click Validate and Test button

• If WSDL is valid then connection will reach to 100%

Now we can use this SOAP connection as a Trigger point in the ICS integration to expose SOAP service.

Switch activity in Oracle Integration Cloud Service

In this article, we will take a look at how to use Switch activity in Oracle ICS. Switch actions allow us to check for a condition and if true, execute a set of additional activities. When the condition isn’t met, the “Otherwise” path is followed.

The Switch activity in Oracle ICS is same as the switch statement in Java. The switch statement is a type of selection control mechanism used to allow the value of a variable or expression to change the control flow of program execution via a multiway branch.

Let's take a quick look how to use Switch activity that allows us to call different options based on the conditions.

Let's create an orchestrated integration in which we'll use the Switch activity.

We will assume that we already have an Orchestrated integration in place. Open the Actions from right side, drop Switch action on the integration canvas

We will notice that two branches have been created by default, one is if and other is default(Otherwise)

Click on the Undefined and click on Edit icon

An expression window will get open, that will allows us to add conditions

Let's drag $tracking_var1 in the first box and enter any literal to match in the second box. Select the operation(>, < ,= ,>= ,<=,!=) from the drop down between these two boxes

 Click + sign to add another condition to the same block

Match drop down allows us to put and/or between two conditions:

• All of: This meas all the condition should meet. It is just like and operator
• Any of: This means any condition should meet. It is just like or operator

Let's select All of here and put the expression as below

Click on the Expression Mode button to see the condition in Expression view

Notice that an and operator between two conditions. It is because we have selected All of from the Match drop down in the preceding options

Click Validate and Close button

We can add multiple cases in a single switch. Let's add one more. Click on Switch activity and + sign

Add the conditions same way we added in previous steps.

Note: ICS UI doesn't support to add case block at the desired position. So please add the cases carefully. Let's suppose, you have added 1,2,3 case block and later you want to add one more case between 1 and 2, you won't be able to add, as new block always adds in the last.