13

I can connect from plsql to database using tns file

Now I want to connect to the database from my Java using JDBC.

What I tried:

I search google and I find that I have to using this connection String:

"jdbc:oracle:thin:@//host:port))/tnsfile)";

My computer name is myPC

The port that is written in the tnsfile is 5151

So I tried this connection String

"jdbc:oracle:thin:@//myPC:5151))/tnsfile"

but I got this Exception

java.sql.SQLRecoverableException: IO ERROR: SO Exception was generated

What am I doing wrong?

How to connect my JDBC to the database using tns file?

msrd0
  • 7,816
  • 9
  • 47
  • 82
Marco Dinatsoli
  • 10,322
  • 37
  • 139
  • 253

3 Answers3

20

You have to set a property named oracle.net.tns_admin to point to the location of the folder containing your tnsnames.ora file. Then you specify the entry from that file after the @ sign in your DB URL. Check example below. You can find more information here: Data sources and URLs - Oracle Documentation

import java.sql.*;

public class Main {
  public static void main(String[] args) throws Exception {
    System.setProperty("oracle.net.tns_admin", "C:/app/product/11.2.0/client_1/NETWORK/ADMIN");
    String dbURL = "jdbc:oracle:thin:@ENTRY_FROM_TNSNAMES";

    Class.forName ("oracle.jdbc.OracleDriver");

    Connection conn = null;
    Statement stmt = null;

    try {
      conn = DriverManager.getConnection(dbURL, "your_user_name", "your_password");

      System.out.println("Connection established");

      stmt = conn.createStatement();

      ResultSet rs = stmt.executeQuery("SELECT dummy FROM dual");

      if (rs.next()) {
        System.out.println(rs.getString(1));
      }
    } catch (Exception e) {
      e.printStackTrace();
    }
    finally {
      if (stmt != null) try { stmt.close(); } catch (Exception e) {}
      if (conn != null) try { conn.close(); } catch (Exception e) {}
    }
  }
}

Example entry from tnsnames.ora file:

my_net_service_name= 
 (DESCRIPTION= 
   (ADDRESS=(some address here))
   (CONNECT_DATA= 
     (SID=some_SID_name)))

Where my_net_service_name string is what you have to subsitite for ENTRY_FROM_TNSNAMES from my Java example.

Przemyslaw Kruglej
  • 8,003
  • 2
  • 26
  • 41
  • first of all, i am using eclipse, secondly, what is the `@ENTRY_FROM_TNSNAMES` ? is it the name of my tnsfile? – Marco Dinatsoli Oct 23 '13 at 09:39
  • It doesn't matter which IDE you are using. Pleas read what tnsnames.ora is here: [About tnsnames.ora file](http://docs.oracle.com/cd/B28359_01/network.111/b28317/tnsnames.htm) Basically, if you (as you've stated) want to connect using tnsnames.ora file, you have to replace the `ENTRY_FROM_TNSNAMES` with one of the `net service names` from `tnsnames.ora` file. Check my edited answer for an example. – Przemyslaw Kruglej Oct 23 '13 at 09:45
  • One small comment. `tnsnames.ora` also supports `include` directive. This one is not implemented by JDBC drivers. A bug report was reported for this few years ago. – ibre5041 Oct 23 '13 at 12:02
  • @Przemyslaw Kruglej - Excellent solution. Its working fine for me. I have one concern here since I am working in my local machine which is running on WindowsXP and our code is deployed in server (UNIX box). I can hard code the 'tns_admin' path and If I deploy the application in UNIX box that will fail/throws exception. Do we have any other alternate instead of hard coding the tns_admin path in above solution? Plz help me – Dinesh M Jan 09 '14 at 09:15
  • 2
    @DineshMailapur There are a few options. You could set the path in the command line and use different scripts to run your app - check [this thread](http://stackoverflow.com/questions/7351533/set-multiple-system-properties-java-command-line) to see how to set a property in command line. Or, you could set environment variable with the same name in each of your systems (win/unix) and get its value when running your program: check [this thread](http://stackoverflow.com/questions/18127534/getting-environment-variable-value-from-java). In the end, you could just read the path from some config file. – Przemyslaw Kruglej Jan 15 '14 at 08:34
6

Try the following: 

System.setProperty("oracle.net.tns_admin", PATH_TO_TNSNAMES.ORA);
Class.forName ("oracle.jdbc.OracleDriver");
dbUrl = "jdbc:oracle:thin:@(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST="+IPHOST+")(PORT="+PORT+"))(CONNECT_DATA=(SERVER = DEDICATED)(SERVICE_NAME="+DBNAME+")))"

conn = DriverManager.getConnection(dbUrl, USERNAME, PASSWORD);

Be sure to have the latest version of ojdbc.jar

user2213042
  • 61
  • 1
  • 1
  • 2
    As you have complete connection description in your JDBC connection string, you don't have to register system property with oracle.net.tns_admin. In fact you are connecting directly ommiting tnsnames.ora file. – lapaczo Sep 14 '18 at 11:10
6

Rather than hard code the path to tnsnames.ora, better to find it from the environment:

public static void setTnsAdmin() {
    String tnsAdmin = System.getenv("TNS_ADMIN");
    if (tnsAdmin == null) {
        String oracleHome = System.getenv("ORACLE_HOME");
        if (oracleHome == null) {
            return; //failed to find any useful env variables
        }
        tnsAdmin = oracleHome + File.separatorChar + "network" + File.separatorChar + "admin";
    }
    System.setProperty("oracle.net.tns_admin", tnsAdmin);
}
Greg Chabala
  • 1,125
  • 2
  • 23
  • 35