3

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

V.J.
  • 31
  • 1
  • 1
  • 2
  • 1
    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 Mar 05 '15 at 12:00

3 Answers3

5

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)
    )
  )
InuSasha
  • 51
  • 1
  • 2
0

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 |

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

Community
  • 1
  • 1
MatteoOreficeIT
  • 190
  • 2
  • 5
-1

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
tom
  • 2,735
  • 21
  • 35
  • 1
    you are right. I'd prefer the same. The oracle client is installed on each of the environments. We are asked not to depend on hostname and port numbers as these can be changed anytime or can vary between different environments. And also to minimize the number of places where the settings have to be changed. Applications written in other languages seem to have no issue with this setup. – V.J. Jan 25 '13 at 11:56
  • If you still want to use TNS i would advice the following link: http://stackoverflow.com/questions/4832056/java-jdbc-how-to-connect-to-oracle-using-service-name-instead-of-sid . But host and port should not change randomly over time. They should be stables for each environment. The one thing you MUST do is externalise the url from you application so it can be set for each environment. – tom Jan 28 '13 at 10:39
  • this link is actually better: http://www.razorsql.com/articles/oracle_jdbc_connect.html – tom Jan 28 '13 at 10:43
  • Thanks for the links Tom. The url is already externalized and we are now using `jdbc:oracle:thin:@//host:port/service_name` format. Guess there is no way we can use Oracle JDBC driver to use TNS Name from tnsnames.ora when there are multiple aliases. – V.J. Jan 31 '13 at 06:37