Using pure TNSNAMES rather than host-based databas

Question

I'm using Oracle JDeveloper 11.1.1.4.0, and I can create database connections (with a type of Oracle (JDBC)) using the thin driver without any problems as long as I'm specifying a host.

For instance, I can connect to a locally-running Oracle XE database by specifying:

Driver: thin
Host Name: localhost
JDBC Port: 1521
Service Name: XE

For connecting to remote databases, I use TNS, and my tnsnames.ora file is set up as below, where MYDATABASE.EXAMPLE.COM is the Oracle Service Identifier I want to use.

MYDATABASE.EXAMPLE.COM=
  (DESCRIPTION=
    (ADDRESS=
      (PROTOCOL=TCP)
      (HOST=testdb.example.com)
      (PORT=1234)
    )
    (CONNECT_DATA=
      (SERVICE_NAME=MYDATABASE.example.com)
    )
  )

Connections to the MYDATABASE.EXAMPLE.COM service work with SQL Developer, SQL Plus, TNSPING etc. from this machine but I can't find a way of specifying this in a JDeveloper database connection without being forced to specify a host.

The reason I don't want to specify a host is the same reason we're using TNS in the first place - the testdb.example.com host will change over time, but the MYDATABASE.EXAMPLE.COM TNS service identifier will not.

If I specify the host testdb.example.com along with the correct TNS alias, the connection works. If I specify the wrong identifier, it fails - it's definitely aware of the contents of my tnsnames.ora file.

Posts like this one seem to suggest that if JDeveloper is aware of TNS_ADMIN, it will use tnsnames.ora, but even using the "Enter Custom JDBC URL" doesn't work, using a string like:

jdbc:oracle:thin:MYDATABASE.EXAMPLE.COM

It still gives the "Please enter a valid value for Host Name" error.

Does anybody know how to create a database connection in JDeveloper without specifying the host?

Answer 1

According to the Oracle JDBC developer's guide, you need to specify the location of the tnsnames.ora file through a (JVM) system property in order to be able to use tnsnames with the thin driver:

http://download.oracle.com/docs/cd/B28359_01/java.111/b31224/urls.htm#BEIDIJCE

You will need to start SQL Developer in such a way that the system property oracle.net.tns_admin is defined.

Adding

AddVMOption -Doracle.net.tns_admin=/path/to/tnsnames.ora

to ide.conf should do the trick (although I have not tried it).