Oracle JDBC Driver Connection Strings

Need to keep these handy.

At the same time, this site is lovely (http://www.connectionstrings.com/) for accessing Databases (like Oracle) from .NET.

For OCI (or Type 2) drivers:

• jdbc:oracle:oci:@TNS_ALIAS
• jdbc:oracle:oci:@<HOST>:1521:<SID>
• jdbc:oracle:oci@//host:1521/service_name
• jdbc:oracle:oci:@(DESCRIPTION= (ADDRESS=(PROTOCOL=TCP)
  (HOST=cluster_alias) (PORT=1521))
  (CONNECT_DATA=(SERVICE_NAME=service_name)))
• jdbc:oracle:oci:@(DESCRIPTION= (LOAD_BALANCE=on)
  (ADDRESS=(PROTOCOL=TCP)(HOST=host1)(PORT=1521))
  (ADDRESS=(PROTOCOL=TCP)(HOST=host2)(PORT=1521))
  (CONNECT_DATA=(SERVICE_NAME=service_name)))

For Thin (or Type 4) drivers:

• jdbc:oracle:thin:username/password@//host:1521/service_name
• jdbc:oracle:thin@//host:1521/service_name
• jdbc:oracle:thin@//cluster-alias:port/service_name
• jdbc:oracle:thin:@<HOST>:1521:<SID>
• jdbc:oracle:thin:@(DESCRIPTION= (ADDRESS=(PROTOCOL=TCP)
  (HOST=cluster_alias) (PORT=1521))
  (CONNECT_DATA=(SERVICE_NAME=service_name)))
• jdbc:oracle:thin:@(DESCRIPTION=(ADDRESS_LIST=
  (ADDRESS=(PROTOCOL=TCP)(port=1521)(host=host1)))
  (connect_data=(INSTANCE_NAME=ORCL)))";
• jdbc:oracle:thin:@(DESCRIPTION=(LOAD_BALANCE=on)
  (ADDRESS=(PROTOCOL=TCP)(HOST=host1) (PORT=1521))
  (ADDRESS=(PROTOCOL=TCP)(HOST=host2) (PORT=1521))
  (CONNECT_DATA=(SERVICE_NAME=service)))

Note the following:

1. For Oracle 8, we will need to use the instance name (SID); for 8i or 9i onwards, we should use the SERVICE_NAME parameter instead.
2. If there is a list of addresses to load balance or failover to, use the ADDRESS_LIST to state the list. ADDRESS should be used otherwise.
3. If Oracle RAC is in used, the last ones above (indicated with LOAD_BALANCE=on) should be used.

 

In order to set-up the data-source for Oracle RAC within WebLogic, use the following:

• For WLS multi-pool setting:

<url>jdbc:oracle:thin:@//host:1521/service_name</url>
<driver-name>oracle.jdbc.xa.client.OracleXADataSource</driver-name>

• For WLS connection pool setting:

DriverName=oracle.jdbc.OracleDriver
URL=jdbc:oracle:thin:@(DESCRIPTION=(LOAD_BALANCE=on)
(ADDRESS=(PROTOCOL=TCP)(HOST=host1) (PORT=1521))
(ADDRESS=(PROTOCOL=TCP)(HOST=host2) (PORT=1521))
(CONNECT_DATA=(SERVICE_NAME=service)))