I am using custom Oracle JDBC driver (ojdbc11.jar) to create a custom config.
We are using TNS names to avoid having to update workflows when host of DB changes.
Therefore we created a custom Oracle config in the database preferences where the database URL only requires the database name.
It is through the tnsnames.ora file shared on the network that the driver finds his way to the database server. Path the tnsnames.ora file is in the TNS_ADMIN environment variable. This has been working fine up to version 5.3.0 of Knime AP. Now that I have upgraded to 5.3.2, I can’t connect anymore.
Connector keeps trying to connect without timeout…
I have tried the same config on Knime AP 5.3.0 and it still works.
I have also tried with with other driver (ojdbc8.jar) and I have the same issue.
When using Knime built-in Oracle driver in 5.3.2, with full host name, it works fine. But I can’t use the TNS.
It would be good that the Oracle connector would allow to use natively tnsnames.ora file. For instance, DBeaver allows to define where the file is but also accepts the TNS_ADMIN environment variable.
Thanks in advance for your help!
Hello @szawadski ,
the Oracle documentation mentions that the oracle.net.tns_admin property needs to be set when using the JDBC Thin driver.
To set the property please append the path to the tnsnames.ora to the end of your knime.ini file like this
-Doracle.net.tns_admin=%ORACLE_HOME%\network\admin
You can find the knime.ini in the root directory of your KNIME Analytics Platform installation.
Bye
Tobias
2 Likes
Thanks @tobias.koetter for the advice, but in Knime 5.3.0, setting the tns_admin as an environment variable in Windows was working fine without having to set it as well in knime.ini file.
I tried but the problem remains:
- I can’t use tns names in the built-in Oracle driver as it forces me to put some host
- I can’t use tns names with my custom driver as it keeps trying to connect even without a decent timeout…
And I don’t see in the 5.3.1 & 5.3.2 changelog what change could have cause this new behaviour…
Hello @szawadski ,
I also checked the internal changelog and couldn’t find any changes affecting the database framework.
I found this Stackoverflow thread that discusses a similar problem. It seems you can also pass the location of the tnsname.ora file as connection property (TNS_ADMIN) which you can set in KNIME via the JDBC Parameters tab. For details see here.
If this also doesn’t help let’s try to narrow down the problem. Did you update an existing KNIME AP from 5.3.0 to 5.3.2 or did you do a new installation? If you did a new installation was it on the same machine or another? If it is on another machine did you use the same driver (including potential companion jars)? Also if you are on another machine can you try to connect to the DB with another tool e.g. DBeaver using the same JDBC driver and tnsnames.ora file set via environment variable to rule out a problem with the network or tnsnames.ora file.
Bye
Tobias
1 Like
