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.

Scheduled Integration in Oracle Integration Cloud Service

In this article, we will take a look at the ability of Oracle Integration Cloud Service to Schedule an orchestrated integration. This is the very cool feature of Oracle ICS that develop a schedule integration with minimal efforts.

Let's take a look at how to achieve Scheduled integration.

From the Integration Screen page, Click on the Create button and select the Orchestration pattern

The first thing, we will see when creating an integration is the Create New Integration Dialog box

As we can see the dialog has a new toggle option which defaults to represent the common approach to Orchestration, that being event triggered by an event or object defined by a connection.

Let's configure the options shown in the Create New Integration Dialog box

• What triggers this integration: Select Schedule. This is the option that creates the Scheduled integration itself
• What do you want to call your integration: Enter any name of choice.
• What does this integration do: Enter description of the integration
• Identifier: Identifier would be picked automatically as soon as, Integration name will be entered. However, we can edit this
• Version: Version of the integration
• Which package does this integration belong to: Enter any package name of your choice. This is optional and can be left blank

With these values set we can complete the step using Create button. We will notice, the first triggering point is schedule with the calendar icon

Let's add any target to complete the integration. Here to complete this integration, we will use FTP adapter and will use writer operation. Complete the FTP adapter configuration wizard. 

Follow the blog which explains how to write files using FTP adapter.

Once the integration is ready, save and close the integration.

We will notice the first icon is the calendar icon which represents the integration is scheduled integration. Let's activate the integration with the help of toggle button which is in front of the integration.

Check the Enable tracing and Include payload button and click on Activate and Schedule button

Upon clicking the Activate and Schedule scheduled button, a new page will get open which will help us to setup the schedules.

Let the Type option Basic. In the Frequency section, click the icon to display a dropdown list for selecting the frequency with which to run the integration. As we define one frequency, we can specify additional values by clicking the icon to the right of the Frequency section.

• Only Once: This is the default selection. This selection clears all settings except for the From field.
• Hours and Minutes: Specify the hours and minutes at which to run the integration.

• Days: Specify the days on which to run the integration

• Weeks: Specify the weeks during which to run the integration

• Months: Specify the months during which to run the integration

Click Save to validate the frequency settings. If there are any errors, a validation message is displayed in the upper left corner that describes how to resolve the errors.

The Advance option allows us to put the iCal expression. Let' try to configure this option. Click on the Advanced radio button.

Enter an iCal expression, and click Validate Expression. For example:

The following expression indicates that this integration runs each month on the 1st, 5th, and 10th days of the month at 5:30 AM, 10:30 AM, 3:30 PM

FREQ=MONTHLY;BYHOUR=1,5,10;BYMINUTE=5,10,15;BYSECOND=30;

We can also define multiple schedule frequencies. The following schedule runs every day 8:00 AM and 8:00 PM. 

Note: This configuration requires two schedules separated by the & sign:

FREQ=DAILY;BYHOUR=08;BYMINUTE=00;BYSECOND=0;

FREQ=DAILY;BYHOUR=20;BYMINUTE=00;BYSECOND=0;

• In the This schedule is effective section, click the link to the right of From.
• A menu is displayed for defining the start date of the schedule.

• If you want to start the integration run when the schedule is activated:
• Click When schedule starts.
• If you want to explicitly set an integration run start date:
• Select Modify start date.
• Click the Calendar icon to select the month, year, and day and the hour, minute, and second at which to start the integration run.
• Click OK.
• In the Until section, click the link to the right.
• A menu is displayed for defining the expiration date.
• If you want the schedule run to never expire:
• Select Never (repeat indefinitely)
• If you want the integration run to have a fixed expiration date:
• Select Choose expiry date.
• Click the Calendar icon to select the month, year, and day and the hour, minute, and second at which to end the integration run.
• Click OK.

Click Save. If successful, a message is displayed in the upper left corner.

Close the integration and click on the Start Schedule button

Once we click Start Schedule button, the page will show the Future run of the integration

Click Close button, it will take us to the integration page. Now the integration has been scheduled to run every day at 8:00 AM and 8:00 PM.

If we want to run the integration manually, just click on the Action menu in front of the Integration name and click Submit Now button

This will submit the integration and will complete all the action defined in the integration.