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:<tns_admin_home>\\oracle\\OracleXE\\app\\oracle\\product\\10.2.0\\server\\NETWORK\\ADMIN
  2. Or setting from the code with the statement: System.setProperty("", "<tns_admin_home>");

Example for the second option:
public final static String ORA_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";
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).


Mike said...

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

Anonymous said...

Thanks for help. :)