0

I seem to be able to connect to the (remote) oracle database using the Run SQL command line but not using JDBC. So, let's say the server name is srv_oracle_prod and the database name is bdcompany.company. In SQL command line I can connect using : 

connect user/password@srv_oracle_prod:1521/bdcompany.company

But when I try 

public class TestRealDatabase {

public static void main(String[] args) {


    try {
        Class.forName("oracle.jdbc.driver.OracleDriver");
        con = DriverManager.getConnection("jdbc:oracle:thin:srv_oracle_prod:1521/bdcompany.company", 
                "user", 
                "password");
    } catch (ClassNotFoundException e) {
        e.printStackTrace();
    } catch (SQLException e) {
        // TODO Auto-generated catch block
        e.printStackTrace();
    }
    }
}

I get this error in eclipse :

java.sql.SQLException: Listener refused the connection with the following error:
ORA-12505, TNS:listener does not currently know of SID given in connect descriptor


at oracle.jdbc.driver.T4CConnection.logon(T4CConnection.java:419)
at oracle.jdbc.driver.PhysicalConnection.<init>(PhysicalConnection.java:536)
at oracle.jdbc.driver.T4CConnection.<init>(T4CConnection.java:228)
at oracle.jdbc.driver.T4CDriverExtension.getConnection(T4CDriverExtension.java:32)
at oracle.jdbc.driver.OracleDriver.connect(OracleDriver.java:521)
at java.sql.DriverManager.getConnection(Unknown Source)
at java.sql.DriverManager.getConnection(Unknown Source)
at theOpenTutorials.TestRealDatabase.main(TestRealDatabase.java:22)
Caused by: oracle.net.ns.NetException: Listener refused the connection with the following error:
ORA-12505, TNS:listener does not currently know of SID given in connect descriptor


at oracle.net.ns.NSProtocol.connect(NSProtocol.java:386)
at oracle.jdbc.driver.T4CConnection.connect(T4CConnection.java:1054)
at oracle.jdbc.driver.T4CConnection.logon(T4CConnection.java:308)
... 7 more

I searched quite a bit but can't find any solution and I am quite the noobie using Oracle.

This is my tnsnames.ora file as requested : 

XE =

      (DESCRIPTION =
        (ADDRESS = (PROTOCOL = TCP)(HOST = PC-HP-08.COMPANY.local)(PORT = 1521))
        (CONNECT_DATA =
          (SERVER = DEDICATED)
          (SERVICE_NAME = XE)
        )
      )

    EXTPROC_CONNECTION_DATA =
      (DESCRIPTION =
        (ADDRESS_LIST =
          (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1))
        )
        (CONNECT_DATA =
          (SID = PLSExtProc)
          (PRESENTATION = RO)
        )
      )

    ORACLR_CONNECTION_DATA = 
      (DESCRIPTION = 
        (ADDRESS_LIST = 
          (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1)) 
        ) 
        (CONNECT_DATA = 
          (SID = CLRExtProc) 
          (PRESENTATION = RO) 
        ) 
      )
Fitz
  • 327
  • 1
  • 6
  • 19
  • 1
    Look at this post here: http://stackoverflow.com/questions/5661610/tns-12505-tnslistener-does-not-currently-know-of-sid-given-in-connect-descript - it is related, I guess. – MWiesner Aug 02 '16 at 14:33
  • @MWiesner I am not sure since I am trying to connect to a remote database. But maybe it is and I'm not understanding. – Fitz Aug 03 '16 at 08:13
  • Try perhaps with the SID in uppercase. Oracle is sometimes case-sensitive where in command-line it changes case automatically. – RealSkeptic Aug 03 '16 at 08:37
  • @RealSkeptic Same error sadly! – Fitz Aug 03 '16 at 08:45

4 Answers4

4

my JDBC thin URLs are usually looking like this:

jdbc:oracle:thin:@SERVER:PORT:SID

it seems to me that you are trying to use the TNS name as the server name. But Oracle thin driver doesn't use TNS (as far as I know).

So my guess is that your JDBC URL should look like this:

jdbc:oracle:thin:@PC-HP-08.COMPANY.local:1521:PLSExtProc
EasterBunnyBugSmasher
  • 1,507
  • 2
  • 15
  • 34
  • I tried `jdbc:oracle:thin:@srv_oracle_prod:1521:bdcompany.company` since the server is **srv_oracle_prod** and the SID **bdcompany.company**. It gave me the same error. – Fitz Aug 03 '16 at 08:49
  • 1
    Seems to work with `jdbc:oracle:thin:@srv_oracle_prod:1521/bdcompany.company` I will check again. – Fitz Aug 03 '16 at 09:05
  • A standard query with the precedent URL actually works, so, I'll accept your answer. – Fitz Aug 03 '16 at 09:27
1

Take a look at your URL:

jdbc:oracle:thin:srv_oracle_prod:1521/bdcompany.company

The error you are getting is because of the SID part. Which is this: bdcompany.company. So, contact your DBA and ask her what's the SID for your Oracle instance.

If you are using tns names, then it might help us helping you if you post tnsnames.ora file contents in your question.

Pablo Santa Cruz
  • 176,835
  • 32
  • 241
  • 292
  • Yes, I had noticed that and I asked a second time my DBA if he was sure about it and he seemed to be. He connected with his own login using the SQL command line with this SID in front of me. I was also able to connect thru SQL command line using my login and this SID so, it should work with jdbc in theory, no ? I have no idea if I am using tns names or not, I will check/ask tomorrow. Thanks for the quick answer – Fitz Aug 02 '16 at 17:44
  • Added tnsnames.ora in case. – Fitz Aug 03 '16 at 08:11
0

Since "XE" is a service name and not a SID, you should use this connection string instead:

jdbc:oracle:thin@//PC-HP-08.COMPANY.local:1521/XE

Maybe this thread would help you: Java JDBC - How to connect to Oracle using Service Name instead of SID

Community
  • 1
  • 1
walen
  • 7,103
  • 2
  • 37
  • 58
  • But I want to connect to a remote oracle server with the server name and the provided SID. Isn't XE local? – Fitz Aug 03 '16 at 08:51
0

Connecting using service name:

DriverManager.getConnection("jdbc:oracle:thin:@//{HOST_NAME}:{PORT}/{SERVICE_NAME}","{USERNAME}","{PASSWORD}");

HOST_NAME, PORT & SERVICE_NAME can be found in "tnsnames.ora" file located in "ORACLE HOME\NETWORK\ADMIN directory"

Reference: http://www.orafaq.com/wiki/JDBC

http://www.orafaq.com/wiki/Tnsnames.ora

GayanDeSilva
  • 81
  • 1
  • 6