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

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

Integration on ICS to expose REST service

This blog is very useful for those who is keen to know, how to create REST services in ICS. This is very straight forward.

After this blog you have learned to how to develop REST service in ICS.

Use Case: Develop REST service that exposes the below json and inserts the same in Oracle DB table.

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

This Use case requires only simple 4 steps to complete:

1. Create REST connection
2. Create DB connection
3. Create an Integration
4. 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 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 the same 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 /)
• Select HTTP verb
• Select Configure a request payload for this endpoint checkbox- This option allows us to assign payload in the next step

• Select Json Sample Radio button: This option allows us to take input in json format. Select inline link to give sample payload

• Enter payload and click OK

• Click Next and Done button

• Click Invokes from right navigation, Select Oracle Databases, drag and drop the TEST_DB_Conn below PostEmployee

• 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.employee_info(employee_id, first_name,last_name,qualificatoin,designation) values(#employee_id,#first_name,#last_name,#qualification,#designation)

• Click Done
• Click on Map to InsertEmployee mapper and click Edit button

• Map the source and target field as shown in the below scree shot. Click Validate and Close button

• Select Action icon and click on Tracking. This is mandatory step and can’t activate integration without this step. Basically this allows us to track the Instance with configured parameter. In our case we will select employeeId field.

• Drag & Drop the employeeId from left panel to first row in Tracking Field and click Done button

• Click Save and Close button from upper right corner

Now our integration is completed and now turn to activate the integration.

      4. Activate Integration

• Click on Activate button

• Select Enable tracking and Include payload check boxes button. This option is useful if we want to see payload in the logs. Click Activate button

• As soon as, integration will be activated, ICS will show you the service end point on the upper side as shown in the below screen shot. Alternatively, you can click on the info icon near to the activate button that will show you the service end point.

This is the time to test the service and see the data in database table. We will use POSTMAN tool to test the REST service

      5. Test the REST service

• Open postman tool
• Enter service end point
• We have to add Basic Auth as ICS service is automatically secured. We need to provide ICS console credentials.

• Enter payload and hit Submit button

• Open database and see record should be inserted into the database

That's done !

Database Adapter in Oarcle ICS(Integration Cloud Service)

In this blog, we are gonna to explain  Oracle database adapter capabilities and how to connect Oracle Database that resides in on-premises network.

Oracle Database Adapter capabilities

The Oracle Database Adapter enables us to integrate the Oracle database residing behind the firewall of on-premises environment with Oracle Integration Cloud Service through use of the on-premises connectivity agent. We can also integrate Oracle database residing on the public cloud and don't require Agent in the picture.

The Oracle Database Adapter provides the following capabilities:

• Invocation of stored procedures.
• Support for non-JDBC (PL/SQL) datatypes in outbound invocations of stored procedures.
• Support  of DML statements and SQL queries: Select, Insert, Update, and Delete.
• Support for generating XSD from PureSQL. 
• Polling for new and updated records for processing in the Oracle database. 
• The Oracle Database Adapter supports distributed polling and multithreading. 
• Support for a logical delete polling strategy. 
• Support for database fault mapping

Connection with Oracle Database

In this we will show you how to make Oracle database connection using ICS that resides on on-premises environment.

Below are the pre-requisite to make DB connection

• ICS agent should be installed(If not then follow the blog)
• On-premise agent should be up and running(If not please follow the blog)
• DB port should be opened on the ICS host machine

Let's create a DB connection in ICS

• Login into the ICS console
• Click on the Connection tile from the ICS home page

• Click on Create button from upper right corner

• Search Oracle Database and select Oracle Database adapter

• Provide below information in the opened dialg box and Click on Create button
• Name: Any meaningful name
• Identifier: Identifier would be picked up automatically based on name, but we can edit.
• Role: Select Trigger and Invoke. We can use this DB connection as a source or target both.

• Click on Configure Connectivity, enter below information and click OK button
• Host: Database host or IP
• Port: Database port number
• SID: Database SID

• Click Configure Security, enter below and click OK button
• UserName: Database user name
• Password: Database password
• Confirm Password: Database password

• Click Configure Agent and select the available agent(TEST_AGENT_GROUP) that we created in our previous blog. Click on Use button

• Click on Test button from upper right corner. If everything is correct then progress bar will reach to 100%

• Click Save and Close button from upper right corner.

Now you are done and ready to use DB connection in your integration.