Database Adapter in Oarcle ICS(Integration Cloud Service)
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.
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.
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
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
Hi Apeksha,
Could you please send the logs and error.
Regards,
Ankur
Were you able to resolve this? I am facing same exact issue – no agent, using AWS RDS – cloud instance of SQL Server express.
This is amazing blog,thanks for good info Oracle SOA Online Training Hyderabd
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
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
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
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
Great Explanation and more useful for anyone.Thanks for sharing…
oracle course in chennai
Wonderful blog & good post.Its really helpful for me, awaiting for more new post. Keep Blogging!
Web Design Training
It is also done to ensure that no person without the proper authorization is allowed to enter into the database. https://www.dbdesigner.net
Hi,
I have created Integration to transfer the data from one Database table to Another database table. But i am getting the below error when i am executing the integration
Error message is – merge {http://schemas.oracle.com/bpel/extension}runtimeFault: oracle.tip.adapter.sa.api.JCABindingException: DBWriteInteractionSpec Execute Failed Exception.
merge failed. Descriptor name: [RL1224VIS.XxicsItemDetails].
Caused by Exception [EclipseLink-3001] (Eclipse Persistence Services – 2.6.4.v20160829-44060b6): org.eclipse.persistence.exceptions.ConversionException
Exception Description: The object [222 1234 111 101 1234 45556], of class [class java.lang.String], could not be converted to [class java.math.BigDecimal].
Internal Exception: java.lang.NumberFormatException.
Please see the logs for the full DBAdapter logging output prior to this exception. This exception is considered not retriable, likely due to a modelling mistake.
Exception Description: The object [222 1234 111 101 1234 45556], of class [class java.lang.String], could not be converted to [class java.math.BigDecimal].
Internal Exception: java.lang.NumberFormatExceptionFault Details :
<err:serviceInvocationError xmlns:err="http://xmlns.oracle.com/cloud/generic/service/fault">
<err:type>DBWriteInteractionSpec Execute Failed Exception</err:type>
<err:title>merge failed. Descriptor name: [RL1224VIS.XxicsItemDetails].</err:title>
<err:detail>Caused by Exception [EclipseLink-3001] (Eclipse Persistence Services – 2.6.4.v20160829-44060b6): org.eclipse.persistence.exceptions.ConversionException
Exception Description: The object [222 1234 111 101 1234 45556], of class [class java.lang.String], could not be converted to [class java.math.BigDecimal].
Internal Exception: java.lang.NumberFormatException.</err:detail>
<err:errorCode>serviceInvocationError</err:errorCode>
<err:remedialAction>Please see the logs for the full DBAdapter logging output prior to this exception.</err:remedialAction>
</err:serviceInvocationError>
Hi,
This is pure cast exception. Here you are trying to pass the string data to a column which is of Integer type in target side.
Make sure you have the same datatype both source and target side.
This comment has been removed by the author.
Hello Ankur,
How to use polling functionality in OIC?
We need to trigger the integration when any records inserted in database.
Do you have any idea of how to achieve this in OIC?
See below article
https://www.techsupper.com/2017/11/database-polling-in-oracle-integration.html
You need to install the OIC agent onto the cloud DB server and also use tunnelling to access it from sqldeveloper.
Thankyou for sharingerp software companies