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?

Answer 1

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

Answer 2

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

Answer 3

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

Answer 4

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

Answer 5

This should be working: jdbc:oracle:thin//hostname:Port/ServiceName=SERVICE_NAME

Answer 6

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

Answer 7

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.

Answer 8

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.