How to Connect to an Oracle Database Using a Service Name in JDBC?

Connecting to Oracle Using Service Name in JDBC

When connecting to an Oracle database using JDBC, there are two main approaches: using the Oracle SID (System Identifier) or the Oracle Service Name. While using the SID is a common practice, some databases may employ a Service Name instead. In this scenario, we will explore how to connect to an Oracle database using the Service Name instead of SID.

Traditionally, the JDBC connection URL to an Oracle database using SID would look like this:

jdbc:oracle:thin:@<hostname>:<port>:<SID-name>

However, when using a Service Name, the URL syntax changes slightly. According to the Oracle documentation, the Thin-style Service Name syntax is as follows:

@//host_name:port_number/service_name

Therefore, to connect to an Oracle database with Service Name 'ABCD', the updated JDBC connection URL would be:

jdbc:oracle:thin:@//<hostname>:<port>/ABCD

For example, if the hostname is 'oracle.hostserver2.mydomain.ca', the port is 1522, and the Service Name is 'ABCD', the complete JDBC connection URL would be:

jdbc:oracle:thin:@//oracle.hostserver2.mydomain.ca:1522/ABCD

Alternatively, you can also specify the TNS (Transparent Network Substrate) name in the JDBC URL, as shown below:

jdbc:oracle:thin:@(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=<hostname>)(PORT=<port>)))(CONNECT_DATA=(SID=<SID-name>)(GLOBAL_NAME=<SID-name>.WORLD)(SERVER=DEDICATED)))

This approach is useful when the Service Name is not publicly accessible or when you need finer control over the connection parameters.

To summarize, when connecting to an Oracle database using JDBC, you can either specify the SID or the Service Name in the connection URL. By following the syntax outlined above, you can successfully establish a connection using the desired method.

The above is the detailed content of How to Connect to an Oracle Database Using a Service Name in JDBC?. For more information, please follow other related articles on the PHP Chinese website!