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

javajdbc

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

Best Answer

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)
    )
  )

Related Solutions

Java – How to create an executable JAR with dependencies using Maven

<build>
  <plugins>
    <plugin>
      <artifactId>maven-assembly-plugin</artifactId>
      <configuration>
        <archive>
          <manifest>
            <mainClass>fully.qualified.MainClass</mainClass>
          </manifest>
        </archive>
        <descriptorRefs>
          <descriptorRef>jar-with-dependencies</descriptorRef>
        </descriptorRefs>
      </configuration>
    </plugin>
  </plugins>
</build>

and you run it with

mvn clean compile assembly:single

Compile goal should be added before assembly:single or otherwise the code on your own project is not included.

See more details in comments.

Commonly this goal is tied to a build phase to execute automatically. This ensures the JAR is built when executing mvn install or performing a deployment/release.

<plugin>
  <artifactId>maven-assembly-plugin</artifactId>
  <configuration>
    <archive>
      <manifest>
        <mainClass>fully.qualified.MainClass</mainClass>
      </manifest>
    </archive>
    <descriptorRefs>
      <descriptorRef>jar-with-dependencies</descriptorRef>
    </descriptorRefs>
  </configuration>
  <executions>
    <execution>
      <id>make-assembly</id> <!-- this is used for inheritance merges -->
      <phase>package</phase> <!-- bind to the packaging phase -->
      <goals>
        <goal>single</goal>
      </goals>
    </execution>
  </executions>
</plugin>

Java – How to connect to Oracle using Service Name instead of SID

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

Thin-style Service Name Syntax

Thin-style service names are supported only by the JDBC Thin driver. The syntax is:

@//host_name:port_number/service_name

For example:

jdbc:oracle:thin:scott/tiger@//myhost:1521/myservicename

So I would try:

jdbc:oracle:thin:@//oracle.hostserver2.mydomain.ca:1522/ABCD

Also, per Robert Greathouse's answer, you can also specify the TNS name in the JDBC URL as below:

jdbc:oracle:thin:@(DESCRIPTION =(ADDRESS_LIST =(ADDRESS =(PROTOCOL=TCP)(HOST=blah.example.com)(PORT=1521)))(CONNECT_DATA=(SID=BLAHSID)(GLOBAL_NAME=BLAHSID.WORLD)(SERVER=DEDICATED)))

Best Answer

Related Solutions

Java – How to create an executable JAR with dependencies using Maven

Java – How to connect to Oracle using Service Name instead of SID

Related Topic