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.