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

Oracle Database Adapter capabilities
The Oracle Database Adapter enables us to integrate the Oracle database residing behind the firewall of the on-premises environment with Oracle Integration Cloud Service through use of the on-premises connectivity agent. We can also integrate the 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 pure SQL. 
  • 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 the 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 the upper right corner
  • Search Oracle Database and select Oracle Database adapter
  • Provide below information in the opened dialog 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 the Use button
  • Click on Test button from the upper right corner. If everything is correct then the progress bar will reach 100%
  • Click the Save and Close button from the upper right corner.
Now you are done and ready to use DB connection in your integration.