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?

score 495 · Accepted Answer · edited Jun 20 '20 at 09:12

495

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

edited Jun 20 '20 at 09:12

Community

1
1

answered Jan 28 '11 at 19:13

Bert F

85,407
12
106
123

Can you incorporate the point about TNSNAMES format per answer from @Robert Greathouse to achieve answer perfection? – Alister Lee Feb 18 '18 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 Apr 03 '18 at 07: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. – neydroydrec Feb 05 '20 at 01:45

score 113 · Answer 2 · edited May 16 '18 at 12:52

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.

Is the service name the name of the schema? – Francisco Souza Oct 31 '22 at 12:00 — Francisco Souza, Oct 31 '22 at 12:00

score 25 · Answer 3 · edited Oct 31 '13 at 08:52

25

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

edited Oct 31 '13 at 08:52

Chacko

1,506
1
20
42

answered Jan 28 '11 at 19:23

Robert Greathouse

1,024
1
10
18

score 22 · Answer 4 · edited Mar 29 '18 at 08:05

22

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

edited Mar 29 '18 at 08:05

Ahmed Ashour

5,179
10
35
56

answered Jan 28 '11 at 19:10

DwB

37,124
11
56
82

3

This didn't work for me, I had to use `jdbc:oracle:thin:@//oracle.hostserver2.mydomain.ca:1522/ABCD`. – WynandB Oct 17 '13 at 23:01
So the IP can be used here instead of `oracle.hostserver2.mydomain.ca`? – neydroydrec Feb 05 '20 at 01:33

score 10 · Answer 5 · edited Oct 19 '15 at 11:15

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

score 1 · Answer 6 · answered May 24 '16 at 12:12

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.

answered May 24 '16 at 12:12

Bhagavathy Vinoth

11
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 Jun 15 '16 at 14:08

score 0 · Answer 7 · edited Jul 28 '18 at 00:10

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

edited Jul 28 '18 at 00:10

AJD

2,400
2
12
22

answered Jul 27 '18 at 22:10

Syk Nar

1
1

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 Jul 27 '18 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:// – access_granted Apr 23 '20 at 02:16

score -2 · Answer 8 · edited Dec 11 '18 at 15:32

-2

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

edited Dec 11 '18 at 15:32

FuSsA

4,223
7
39
60

answered Dec 11 '18 at 13:11

Kamesh Murali

1

1

Error: "Invalid Oracle URL specified", 11g/ojdbc7 combination. – access_granted Apr 23 '20 at 02:19

How to connect to Oracle using Service Name instead of SID

8 Answers8

Linked

Related