Monday 20 July 2009

Oracle Thin JDBC with tnsnames

Today I had to change my java project in a way that it uses Tnsnames for database resolving instead of a jdbc-url.

I googled around and found my answer here. This solution works with the jdbc-drivers from Oracle 10.2 onwards.

The solution basically is that you set the java system property "TNS_ADMIN". This can be done in two ways:
  1. By giving it as a java command line paramater: -Doracle.net.tns_admin=<tns_admin_home> eg.-Doracle.net.tns_admin=c:\\oracle\\OracleXE\\app\\oracle\\product\\10.2.0\\server\\NETWORK\\ADMIN
  2. Or setting from the code with the statement: System.setProperty("oracle.net.tns_admin", "<tns_admin_home>");

Example for the second option:
public final static String ORA_NET_TNS_ADMIN = "oracle.net.tns_admin";
...
// fetch tnsAdmin from a property file. Here hard-coded as an example:
public String tnsAdmin = "c:\\oracle\\OracleXE\\app\\oracle\\product\\10.2.0\\server\\NETWORK\\ADMIN";
...
System.setProperty(ORA_NET_TNS_ADMIN,tnsAdmin);
For an XE database for example the jdbc-url will be: jdbc:oracle:thin:@xe

The TNS_ADMIN property has to be set with the location/folder where the tnsnames.ora can be found. Not with the full-path to the tnsnames.ora.
I tried this with the database drivers from jdeveloper10.1.3.4 (ojdbc14.jar). But apparently they are from the 10.1 era. It does work with the one delivered with SqlDeveloper 1.5.x (ojdbc5.jar).

2 comments :

Mike said...

Thanks mate - that last line about it only working with ojdbc5 fixed my problem. Thanks

Anonymous said...

Thanks for help. :)