Oracle connection to multiple host or via firewall

So I have a bit of an odd scenario, one of the Oracle DB’s I need to connect to is routed in the below fashion in my tnsnames.ora, I can connect to this DB in Toad, but obviously this doesn’t quite fit the standard URL Template for Oracle… any ideas how I might make this connection in KNIME?

(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=xxx.xxx.xxx.xxx)(PORT=1521))(ADDRESS=(PROTOCOL=TCP)(HOST=xxx.xxx.xxx.xxx)(PORT=1521)))(CONNECT_DATA=(SERVICE_NAME=myservicename))(SOURCE_ROUTE=YES))

Thanks in advance,
B

Hello bve,
you should be able to use the following connection URL in the generic DB Connector node just make sure to select Oracle as database type and select an appropriate Oracle driver e.g. ojdbc8.jar. For further details on how to register the Oracle driver see the db documentation.

jdbc:oracle:thin:@(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=xxx.xxx.xxx.xxx)(PORT=1521))(ADDRESS=(PROTOCOL=TCP)(HOST=xxx.xxx.xxx.xxx)(PORT=1521)))(CONNECT_DATA=(SERVICE_NAME=myservicename))(SOURCE_ROUTE=YES))

Another way is to use the service name directly which would result in the following URL

jdbc:oracle:thin:@NET_SERVICE_NAME

To use the service name you have to tell KNIME where your tnsnames.ora file is located. To do so add the following line to the end of your knime.ini file which is located in the installation folder
oracle.net.tns_admin",

-Doracle.net.tns_admin=%ORACLE_TNS_ADMIN%

If you want to do this you can change the JDBC URL template of the registered Oracle driver to the following template

jdbc:oracle:thin:@<host>

In the Oracle Connector enter the service name into the Host field which will be then used in the template as service name to connect to the database.
Bye
Tobias

1 Like

Thanks Tobias, I’ll give these each a try once I am back to work and provide a follow-up with my outcomes.

Thanks again,
Burke

This topic was automatically closed 182 days after the last reply. New replies are no longer allowed.