296

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?

8 Answers 8

504

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)))
3
  • Can you incorporate the point about TNSNAMES format per answer from @Robert Greathouse to achieve answer perfection? Commented Feb 18, 2018 at 23:11
  • For me it didn't work with the @, I had to use jdbc:oracle:thin://myhost:1521/myservicename, but also I didn't provide user credentials
    – Daniel
    Commented Apr 3, 2018 at 7:46
  • 1
    I've been trying to figure out how to connect to Oracle using JDBC thin driver on Google App Script and tried a number of syntax without success. jdbc:oracle:thin:USER/PWD@//my.ip.address:1521/SERVICENAME or jdbc:oracle:thin:@//my.ip.address.1521/SERVICENAME , with username and password as arguments to jdbc.getConnection(). Still puzzling. Commented Feb 5, 2020 at 1:45
113

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.

1
  • Is the service name the name of the schema? Commented Oct 31, 2022 at 12:00
26

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

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

2
  • 3
    This didn't work for me, I had to use jdbc:oracle:thin:@//oracle.hostserver2.mydomain.ca:1522/ABCD.
    – WynandB
    Commented Oct 17, 2013 at 23:01
  • So the IP can be used here instead of oracle.hostserver2.mydomain.ca? Commented Feb 5, 2020 at 1:33
10

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

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.

1
  • More importantly this allows you to fully specify the connection URL unlike the Thin Driver. Funny thing is, you still have to use the thin driver URL to get it to work (thin style service names only supported by JDBC thin driver). Plenty of examples posted here.
    – Edi Bice
    Commented Jun 15, 2016 at 14:08
0

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
2
  • 1
    Please consider adding some more information to your answer describing a little more on what worked/did not work - what did you observe or know about why this works?
    – AJD
    Commented Jul 27, 2018 at 22:17
  • 1
    Worth noting is - you are using a specific driver. Attempting to use the Oracle's thin driver returns: No suitable driver found for jdbc:dag:oracle:// Commented Apr 23, 2020 at 2:16
-2

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

1
  • 1
    Error: "Invalid Oracle URL specified", 11g/ojdbc7 combination. Commented Apr 23, 2020 at 2:19

Not the answer you're looking for? Browse other questions tagged or ask your own question.