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.

 

Logs in Oracle Integration Cloud Services(ICS)

The Integration Cloud Service dashboard provides a view into the activity stream for running integrations, such as details about successful responses received from the target application, successful responses sent to the source application, and target application invocations.

To monitor activity streams and download log files:

• On the Integration Cloud Service toolbar, click Monitoring

• Click Dashboards

• The dashboard appears and shows processing information for all  running integrations

• Click on  Download Diagnostics Logs to download the ics-logs.zip file for offline analysis

• Unzip the file and see the below structure

javax.net.ssl.SSLHandshakeException: sun.security.validator.ValidatorException: PKIX path building failed

Sometimes users deal with "sun.security.validator.ValidatorException: PKIX path building failed" with the exception and don't know how to solve the issue.

Problem: This error occurred usually when we try to consume secured services using java client. Below is the stack trace that we usually get

 sun.security.validator.ValidatorException:

 PKIX path building failed: sun.security.provider.certpath.SunCertPathBuilderException:

 unable to find valid certification path to requested target

 javax.net.ssl.SSLHandshakeException: sun.security.validator.ValidatorException: PKIX path   building failed: sun.security.provider.certpath.SunCertPathBuilderException: unable to find valid   certification path to requested target

                sun.security.ssl.Alerts.getSSLException(Alerts.java:192)

                sun.security.ssl.SSLSocketImpl.fatal(SSLSocketImpl.java:1937)

                sun.security.ssl.Handshaker.fatalSE(Handshaker.java:302)

                sun.security.ssl.Handshaker.fatalSE(Handshaker.java:296)

                sun.security.ssl.ClientHandshaker.serverCertificate(ClientHandshaker.java:1478)

                sun.security.ssl.ClientHandshaker.processMessage(ClientHandshaker.java:212)

                sun.security.ssl.Handshaker.processLoop(Handshaker.java:979)

                sun.security.ssl.Handshaker.process_record(Handshaker.java:914)

                sun.security.ssl.SSLSocketImpl.readRecord(SSLSocketImpl.java:1050)

                sun.security.ssl.SSLSocketImpl.performInitialHandshake(SSLSocketImpl.java:1363)

                sun.security.ssl.SSLSocketImpl.startHandshake(SSLSocketImpl.java:1391)

Cause:

Main reason for the issue is that SSL certificate has not been installed properly

Solution:

1) Download the InstallCert.java file from here

2) Compile the file using the command as below:
     
              javac InstallCert.java

3) Add Trusted Keystore

Run InstallCert.java using java InstallCert.java, with the hostname and https port, and press “1” when asking for input. It will add the “localhost” as a trusted keystore, and generate a file named “jssecacerts“

C:\Ankur>java InstallCert localhost:443

Loading KeyStore C:\Program Files (x86)\Java\jre1.8.0_91\lib\security\cacerts...

Opening connection to localhost:443...

Starting SSL handshake...

javax.net.ssl.SSLHandshakeException: sun.security.validator.ValidatorException: PKIX path building failed: sun.security.provider.certpath.SunCertPathBuilderException: unable to find valid certification path to requested target

        at sun.security.ssl.Alerts.getSSLException(Unknown Source)

        at sun.security.ssl.SSLSocketImpl.fatal(Unknown Source)

        at sun.security.ssl.Handshaker.fatalSE(Unknown Source)

        at sun.security.ssl.Handshaker.fatalSE(Unknown Source)

        at sun.security.ssl.ClientHandshaker.serverCertificate(Unknown Source)

        at sun.security.ssl.ClientHandshaker.processMessage(Unknown Source)

        at sun.security.ssl.Handshaker.processLoop(Unknown Source)

        at sun.security.ssl.Handshaker.process_record(Unknown Source)

        at sun.security.ssl.SSLSocketImpl.readRecord(Unknown Source)

        at sun.security.ssl.SSLSocketImpl.performInitialHandshake(Unknown Source)

        at sun.security.ssl.SSLSocketImpl.startHandshake(Unknown Source)

        at sun.security.ssl.SSLSocketImpl.startHandshake(Unknown Source)

        at InstallCert.main(InstallCert.java:59)

Caused by: sun.security.validator.ValidatorException: PKIX path building failed: sun.security.provider.certpath.SunCertPathBuilderException: unable to find valid certification path to requested target

        at sun.security.validator.PKIXValidator.doBuild(Unknown Source)

        at sun.security.validator.PKIXValidator.engineValidate(Unknown Source)

        at sun.security.validator.Validator.validate(Unknown Source)

        at sun.security.ssl.X509TrustManagerImpl.validate(Unknown Source)

        at sun.security.ssl.X509TrustManagerImpl.checkTrusted(Unknown Source)

        at sun.security.ssl.X509TrustManagerImpl.checkServerTrusted(Unknown Source)

        at InstallCert$SavingTrustManager.checkServerTrusted(InstallCert.java:161)

        at sun.security.ssl.AbstractTrustManagerWrapper.checkServerTrusted(Unknown Source)

        ... 9 more

Caused by: sun.security.provider.certpath.SunCertPathBuilderException: unable to find valid certification path to requested target

        at sun.security.provider.certpath.SunCertPathBuilder.build(Unknown Source)

        at sun.security.provider.certpath.SunCertPathBuilder.engineBuild(Unknown Source)

        at java.security.cert.CertPathBuilder.build(Unknown Source)

        ... 17 more

Server sent 1 certificate(s):

 1 Subject CN=Ankur-Jain

   Issuer  CN=Ankur-Jain

   sha1    d3 b9 1c 0d 37 15 43 eb 42 08 6e 28 f9 b9 14 51 b4 5d 80 fb

   md5     d0 0e b8 cf 3e af ee a7 75 96 7b 65 d0 a8 83 1f

Enter certificate to add to trusted keystore or 'q' to quit: [1]

1

[

[

  Version: V3

  Subject: CN=Ankur-Jain

  Signature Algorithm: SHA256withRSA, OID = 1.2.840.113549.1.1.11

  Key:  Sun RSA public key, 2048 bits

  modulus: 29097399406335862648603478718093032258106380849402774151925502342615178908568117666690377193826203615479790565960821376205940928458371313760609133959349461286885160113266037440511976960329613437120185785368606059740742844262213088886805653789914216591685771502790972206462685411556415317772895044855789311041156987953579907954163194290953047131042696647502021116508936286048203642300999439036533705336558235724297279695584190506372666605140794146250538573493778513998757194609517715519451722657765734837367627217395785262158854804147187586905921058319581450357763769002145000821758050508892633704219266564731848702777

  public exponent: 65537

  Validity: [From: Mon Jun 12 17:27:51 IST 2017,

               To: Thu Jun 10 17:27:51 IST 2027]

  Issuer: CN=Ankur-Jain

  SerialNumber: [    ef54124a a7408795]

Certificate Extensions: 3

[1]: ObjectId: 2.5.29.37 Criticality=false

ExtendedKeyUsages [

  serverAuth

]

[2]: ObjectId: 2.5.29.15 Criticality=false

KeyUsage [

  Key_Encipherment

  Data_Encipherment

]

[3]: ObjectId: 2.5.29.17 Criticality=false

SubjectAlternativeName [

  DNSName: Ankur-Jain

]

]

  Algorithm: [SHA256withRSA]

  Signature:

0000: A2 AD A1 B5 3C 3A 9D A4   DD 35 CC 7B 79 91 D2 FC  ....<:...5..y...

0010: 91 53 8A 43 4B 55 4C DF   D6 CB 5C 82 8F 1E 44 C5  .S.CKUL...\...D.

0020: D6 64 40 1B 7D FD 75 E7   D6 22 98 24 52 56 83 7C  .d@...u..".$RV..

0030: B8 89 F5 A6 DD 5F 5F A8   19 51 59 9D 6A DD 0E 69  .....__..QY.j..i

0040: 59 68 AD E8 2B EB 73 D3   F8 16 15 2F 1D FF 84 77  Yh..+.s..../...w

0050: 62 2F 05 24 78 EE 5E F9   13 81 AF 79 45 B0 D7 34  b/.$x.^....yE..4

0060: 19 54 52 15 39 0C 4B D5   6C E2 9E 78 38 7A 4C 7E  .TR.9.K.l..x8zL.

0070: 57 16 E3 AB F9 DC 56 07   13 D5 6B 2C E1 6D E5 1C  W.....V...k,.m..

0080: 3C DC 05 94 18 BE 99 7D   0B C6 51 C6 E2 8F DD 7A  <.........Q....z

0090: 32 6A C3 5B 49 12 F5 AD   8B 15 C7 D0 B7 F7 DC 7F  2j.[I...........

00A0: C0 9C 6E 9F 35 0D 33 92   53 84 C1 79 2F A8 F5 C5  ..n.5.3.S..y/...

00B0: F9 F5 F1 4F 7A F7 22 54   43 88 DF 83 D5 6B CD E7  ...Oz."TC....k..

00C0: 4C D2 49 66 D0 F5 57 40   0E DD 87 B4 8E CB 6B BD  L.If..W@......k.

00D0: C4 1F 99 E8 5B 92 5B 10   FB C6 DD EA 70 F7 B2 ED  ....[.[.....p...

00E0: 5E CF 02 44 5B 40 28 0B   97 72 01 EA 59 F1 F7 38  ^..D[@(..r..Y..8

00F0: 71 1C BD 48 32 93 7E 91   1F 8B 29 85 E2 A3 8B 8E  q..H2.....).....

]

Added certificate to keystore 'jssecacerts' using alias 'localhost-1' 

3) Verify Trusted Keystore

Check if the certificate has been added successfully or not using the same command again

C:\Ankur>java InstallCert localhost:443

Loading KeyStore jssecacerts...

Opening connection to localhost:443...

Starting SSL handshake...

No errors, certificate is already trusted

Server sent 1 certificate(s):

 1 Subject CN=Ankur-Jain

   Issuer  CN=Ankur-Jain

   sha1    d3 b9 1c 0d 37 15 43 eb 42 08 6e 28 f9 b9 14 51 b4 5d 80 fb

   md5     d0 0e b8 cf 3e af ee a7 75 96 7b 65 d0 a8 83 1f

Enter certificate to add to trusted keystore or 'q' to quit: [1]

q

KeyStore not changed

4) Copy jssecacerts: Copy the generated jssecacerts file at $JAVA_HOME/jre/lib/security folder

5) Invoke the service client and the problem went away.

Bind query parameter in database adapter in Oracle Integration Cloud Service

In this blog, we will show you how to use bind parameter in SQL query when use Oracle Database adapter.

Bind parameters are required to get the result based on the certain parameters.

Use Case: Develop REST service that takes EmployeeId as a input parameter and return the response as below:

{  
   "employeeId":"ABC@123",
   "firstName":"Ankur",
   "lastName":"Jain",
   "Qualification":"MCA",
   "Designation":"Sr. Manager"
}

This Use case requires only simple 4 steps to complete:

• Create REST connection
• Create DB connection
• Create an Integration
• Activate the Integration

Let's go step by step

1. Create REST connection: The REST connection we are gonna to create will work as a Trigger point. We have already showcased the same how to create REST connection in one of my blog. Please check it out before moving forward.
2. Create DB connection: DB connection will work as a Target point. We have already showcased how to create Oracle DB connection in one of my blog. Please check it out before moving forward.
3. Create an Integration: Follow below steps to create an 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 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 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

• Integration pane will look like

• Drag the TEST_REST_Conn Connection on the canvas from REST connection

• Enter below information and Click Next
• Name of the end point
• Relative URI(must start with /) for example (/getemployee)
• Select HTTP verb : GET
• Select Add and review parameter for this endpoint radio button
• Select Configure this endpoint to receive the response radio button

• Select + Sign from Specify Query Parameters section to add request parameter. Enter EmployeeId in Name column and select string in Data Type column then click Next button

• Select JSON sample radio button, click inline link and enter below payload. Click OK button then Next

{  

   "employeeId":"ABC@123",
   "firstName":"Ankur",
   "lastName":"Jain",
   "Qualification":"MCA",
   "Designation":"Sr. Manager"
}

• Select Done button
• Click Invokes from right navigation, Select Oracle Databases, drag and drop the TEST_DB_Conn below Map to GetEmployee

• Enter below information and Click Next
• Enter endpoint name(SelectEmployees) 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

select employee_id, first_name,last_name,qualificatoin,designation from apps.employee_info where employee_id = #employeeId

Note: Use # sign to define the binding parameters

• Click Done button on the last screen
• Edit the SelectEmployees mapper and Map EmployeeId parameter to employeeId

• From Actions, drop Map activity just below the SelectEmployees 

• Expand SelectEmployees-> SelectEmployeesOutputCollection -> SelectEmployeeOutput and map the fields as shown in the below screen shot

Integration is completed. Let activate the integration and test using POSTMAN tool

End point would be reachable on below URL:

https://<ICS_URL>/integration/flowapi/rest/REST_SERVICE/v01/metadata/getemployee?EmployeeId=123

Add Basic Authentication in the POST and hit the request