Java JDBC - How to connect to Oracle using Service Name instead of SID

Question

I have a Java application that uses JDBC  via JPA  that was connecting to a development database using hostname  port and Oracle SID  like this   jdbc oracle thin  oracle hostserver1 mydomain ca 1521 XYZ  XYZ was the Oracle SID  Now I need to connect to a different Oracle database that does not use a SID  but uses an Oracle  Service Name  instead   I tried this but it doesn t work   jdbc oracle thin  oracle hostserver2 mydomain ca 1522 ABCD  ABCD is the Service Name of the other database   What am I doing wrong

User · Answer

When using dag instead of thin  the syntax below pointing to service name worked for me   The jdbc thin solutions above did not work   jdbc dag oracle   HOSTNAME 1521 ServiceName SERVICE NAME

User · Answer

This should be working  jdbc oracle thin  hostname Port ServiceName SERVICE NAME

User · Answer

Try this  jdbc oracle thin  oracle hostserver2 mydomain ca 1522 ABCD  Edit  per comment below this is actualy correct  jdbc oracle thin    oracle hostserver2 mydomain ca 1522 ABCD  note the      Here is a link to a helpful article

User · 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

User · Answer

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

User · Answer

In case you are using eclipse to connect oracle without SID  There are two drivers to select i e   Oracle thin driver and other is other driver  Select other drivers and enter service name in database column  Now you can connect directly using service name without SID

User · Answer

So there are two easy ways to make this work  The solution posted by Bert F works fine if you don t need to supply any other special Oracle-specific connection properties  The format for that is   jdbc oracle thin    HOSTNAME PORT SERVICENAME   However  if you need to supply other Oracle-specific connection properties then you need to use the long TNSNAMES style  I had to do this recently to enable Oracle shared connections  where the server does its own connection pooling   The TNS format is   jdbc oracle thin   description  address  host HOSTNAME  protocol tcp  port PORT   connect data  service name SERVICENAME  server SHARED      If you re familiar with the Oracle TNSNAMES file format  then this should look familiar to you  If not then just Google it for the details

User · Answer

This discussion helped me resolve the issue I was struggling with for days  I looked around all over the internet until I found the answered by Jim Tough on May 18  11 at 15 17  With that answer I was able to connect  Now I want to give back and help others with a complete example  Here goes   import java sql      public class MyDBConnect        public static void main String   args  throws SQLException            try                String dbURL    jdbc oracle thin   DESCRIPTION  ADDRESS LIST  ADDRESS  PROTOCOL TCP  HOST whatEverYourHostNameIs  PORT 1521    CONNECT DATA  SERVICE NAME yourServiceName                  String strUserID    yourUserId               String strPassword    yourPassword               Connection myConnection DriverManager getConnection dbURL strUserID strPassword                Statement sqlStatement   myConnection createStatement                String readRecordSQL    select   from sa work order where WORK ORDER NO    1503090                   ResultSet myResultSet   sqlStatement executeQuery readRecordSQL               while  myResultSet next                      System out println  Record values      myResultSet getString  WORK ORDER NO                               myResultSet close                myConnection close               catch  Exception e                System out println e

[java] Java JDBC - How to connect to Oracle using Service Name instead of SID

Examples related to java

Examples related to oracle

Examples related to jdbc

Examples related to connection-string

Examples related to service-name