Connect Oracle DB using service_name instead of SID using ojdbc14.jar driver in WebLogic Server 6.1 SP1 with JDK 3

Question

While Working on a legacy application that first file date back to year 2005. It used to create connection pool that is mapped to DataSource that application connects with,

URL: jdbc:oracle:thin:@host.test.intranet:1521:service_name
Driver Classname:oracle.jdbc.driver.OracleDriver
Properties(key=value):
user=makeduser
password=maskedpassword
dll=ocijdbc8
protocol=thin

ACLName: null

Recently, the db got rehosted and the new connection details changed from SID to Service_name

While trying to use same format "host"port:sid"

The error that it returns when weblogic server is started Cannot startup connection pool "veroPool" weblogic.common.ResourceException:Could not >create pool connection. The DBMS driver exception was:java.sql.SQLException: Io exception: >Connection refused(DESCRIPTION=(TMP=)(VSNNUM=318767104)(ERR=12505)(ERROR_STACK=(ERROR=>(CODE=12505)(EMFI=4))))

And When trying to use following format:

jdbc:oracle:thin:@//NEWHOST.TEST.INTRANET:1521/NEW-SERVICE_NAME

Error returned is:

Cannot startup connection pool "veroPool" weblogic.common.ResourceException:Could not create pool connection. The DBMS driver exception was: java.sql.SQLException: Io exception: Invalid connection string format, a valid format is: "host:port:sid"

Answer 1

This driver version doesn't support the service name passed in the url in this format, so you have to use the SID. Try to connect to the DB and get the SID using the following query:

select sys_context('userenv','instance_name') from dual; 

Then you can use the SID returned from the query in your connection url:

jdbc:oracle:thin:@host.test.intranet:1521:SID

Alternatively you can try with the following syntax to specify your connection which suports service name for this driver version:

jdbc:oracle:thin:@(DESCRIPTION =(ADDRESS = (PROTOCOL = TCP)(HOST = <HOST>)(PORT = <PORT>))(CONNECT_DATA =(SERVER = DEDICATED)(SERVICE_NAME = <SERVICE_NAME>)))