Friday, 7 July 2017

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.

12 comments:

  1. Hi, could you please explain how to connect to cloud database? I tried connecting to cloud db without Connectivity agent and received the following error response,

    Unable to test connection "CLOUDDB". [Cause: CLOUD-0005]
    CLOUD-0005: Unable to establish connection. Please check connection parameters
    IO Error: The Network Adapter could not establish the connection

    Thank you.

    ReplyDelete
    Replies
    1. Hi,

      Even i have never tried with cloud database. However would like to ask few questions:

      1) Which database you are trying to connect ?
      2) R you able to connect the database with some client(SQL developer)

      Errors simply says that, your db is not reachable to ICS.

      Suggestions:

      1) Please try to put hostname and IP of database one by one
      2) Try to enter SID and service name one by one


      Regards,
      Ankur

      Delete
    2. Were you able to resolve this? I am facing same exact issue - no agent, using AWS RDS - cloud instance of SQL Server express.

      Delete
  2. I am facing the same issue.
    To answer Ankur's questions :-
    1) Trying to connect to Cloud database created on Oracle cloud
    2) Yes, the database is connected to sql developer

    Regards
    Apeksha

    ReplyDelete
    Replies
    1. Hi Apeksha,

      Could you please send the logs and error.

      Regards,
      Ankur

      Delete
  3. The article is well-written. It is quite informative and attractive. We are the world's leading email database vendor. We at Pegasi Media Group deal with all types of companies and industries irrespective of their sizes and the type of their businesses. Our customized email database contains the customer's geographical location, their interests and other details. Our Contact Discovery Services successfully finds out the customer information and their product or service requirements, which allows you to know their requirements.Database Marketing Services

    ReplyDelete
  4. Research your Oracle DB Connection Issue? Contact to Database Administration for Oracle
    Right when any customer endeavoring to relate java application to Oracle db using JDBC thin client, meanwhile your structure name was sent to db server without have name and issue occurring in your affiliation. Consequently, in case you are not prepared to comprehend your affiliation issue by then quickly associate with Cognegic's Online Oracle DB Support or Oracle Database Solution. We have exceedingly dedicated and experienced gathering who take mind your issues and commit you error free.
    For More Info: https://cognegicsystems.com/
    Contact Number: 1-800-450-8670
    Email Address- info@cognegicsystems.com
    Company’s Address- 507 Copper Square Drive Bethel Connecticut (USA) 06801

    ReplyDelete
  5. Unable to Distribute Oracle Database? Contact to DB Recovery Support to take care of this issue
    In the event that you need to circulate Oracle database 11g on 3 servers however in the event that you don't have the foggiest idea about that procedure how it should be possible at that point rapidly call our specialists i.e. DB Recovery Services or Exchange Database Recovery. We at Cognegic are pioneers in Remote DBA Services and offering most trusted and solid help. Here you can quickly distinguish any issue and resolve them inside couple of minutes. Here our expert specialists can without much of a stretch oversee and screen the execution of your MS SQL Server.
    For More Info: https://cognegicsystems.com/
    Contact Number: 1-800-450-8670
    Email Address- info@cognegicsystems.com
    Company’s Address- 507 Copper Square Drive Bethel Connecticut (USA) 06801

    ReplyDelete
  6. The most effective method to Find Oracle Database Connection Details with Remote DBA Services
    Well! In the event that you are new on Oracle Database and in the event that you make another association through SQL designer however in the event that you are not ready to get the association points of interest, have name and port at that point rapidly contact to Database Administration for Oracle or Online Oracle DB Support. Aside from that on the off chance that you have any issue with respect to Oracle like association issue, establishment issue, arrangement issue et cetera then you can likewise contact to our Oracle Database Solution and experience the best help through most recent innovation.
    For More Info: https://cognegicsystems.com/
    Contact Number: 1-800-450-8670
    Email Address- info@cognegicsystems.com
    Company’s Address- 507 Copper Square Drive Bethel Connecticut (USA) 06801

    ReplyDelete
  7. Great Explanation and more useful for anyone.Thanks for sharing...
    oracle course in chennai

    ReplyDelete
  8. Wonderful blog & good post.Its really helpful for me, awaiting for more new post. Keep Blogging!
    Web Design Training

    ReplyDelete