How to connect to the Oracle database using JDBC thin driver with TNSNames Alias Syntax

3
votes

I am trying to connect to Oracle (11.2.0.2.0) database using the Oracle JDBC thin driver(ojdbc6.jar for 11.2.0.2.0) and the following JDBC URL syntax:

jdbc:oracle:thin:@abcd

where 'abcd' is defined in my tnsnames.ora file as shown below:

    abcd, abcd.world, abcd.dk.xyz.com  =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = abcd.dk.xyz.com)(PORT = 1521))
    (CONNECT_DATA =
      (SERVICE_NAME = abcd)
    )
  )

I have provided the VM option -Doracle.net.tns_admin=/etc/tnsnames.ora as per Oracle® Database JDBC Developer's Guide and Reference.

I am getting the following error when I run the application:

    Listener refused the connection with the following error:
ORA-12504, TNS:listener was not given the SID in CONNECT_DATA

But when I remove the aliases abcd.world and abcd.dk.xyz.com from tnsnames.ora, my application is able to connect to the database.

Is there an issue with ojdbc driver when there are multiple aliases in tnsnames.ora file?

My JDK version is 1.6.0_31.

Thanks, VJ

3
javajdbc
oracle.net.tns_admin should point onto a directory not into a file. so it should be -Doracle.net.tns_admin=/etc. Also note that database cluster might have more than one hostname plus there are dozens of connection parameters DBAs might want to setup. tnsnames.ora really should be prefered way. JDBC urls are too "stupid". – ibre5041

3 Answers

5
votes

It's look like, the Oracle JDBC driver can not work with multiple service names. Use these entry

    abcd =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = abcd.dk.xyz.com)(PORT = 1521))
    (CONNECT_DATA =
      (SERVICE_NAME = abcd)
    )
  )
    abcd.world, abcd.dk.xyz.com  =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = abcd.dk.xyz.com)(PORT = 1521))
    (CONNECT_DATA =
      (SERVICE_NAME = abcd)
    )
  )
0
votes

I can successfully connect to oracle via tnsnames.ora in the following way

  1. download and extract flyway

  2. download latests ojdbcX.jar from oracle site

  3. put the file in the drivers dir into the flyway folder

  4. put flyway folder (where you find flyway cli script) in path (es: on linux export PATH=/home/matteo/opt/flyway

  5. edit the flyway binary /home/matteo/opt/flyway/flyway putting in JAVA_ARGS="-Doracle.net.tns_admin= pointing to the folders containing the tnsnames.ora

  6. NOTE: the flyway script for linux has a bug in the following lines JAVA_ARGS="-Djava.security.egd=file:/dev/../dev/urandom $JAVA_ARGS" you need to append $JAVA_ARGS to not be overwritten

  7. edit a file in your project folder named as you wish for example flyway.conf

  8. test flyway with this command

    flyway -configFile=flyway.conf info

I receive this output

Flyway 4.0.3 by Boxfuse

Database: jdbc:oracle:thin:@PSR.LAB_CERT (Oracle 11.2)

+---------+-------------+---------------------+---------+

| Version | Description | Installed on | State |

+---------+-------------+---------------------+---------+

| No migrations found |

+---------+-------------+---------------------+---------+

-1
votes

Ideally you should not connect via TNS because you need to install an oracle client on your machine.

If you use a connection url like follows you do not need an oracle client:

jdbc:oracle:thin:@//host:port/service_name