Using pure TNSNAMES rather than host-based database connections in Oracle JDeveloper
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=
(DE开发者_如何学运维SCRIPTION=
(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?
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).
精彩评论