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?

Atlantic

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

Copy

Can you incorporate the point about TNSNAMES format per answer from @Robert Greathouse to achieve answer perfection? – Limitation

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 – Aramenta

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. – Boatload

Answer 2

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.

Atlantic

Is the service name the name of the schema? – Schifra

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

Anadiplosis

Answer 4

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

Dysphasia

This didn't work for me, I had to use jdbc:oracle:thin:@//oracle.hostserver2.mydomain.ca:1522/ABCD. – Greave

So the IP can be used here instead of oracle.hostserver2.mydomain.ca? – Boatload

Answer 5

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

Smackdab

Answer 6

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.

Broder

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. – Travistravus

Answer 7

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

Doggery

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? – Rhythmist

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:// – Bromidic

Answer 8

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

Guernica

Error: "Invalid Oracle URL specified", 11g/ojdbc7 combination. – Bromidic