… or how to setup your Oracle XE database so that JDBC database urls will work like they traditionally have.
For many years Oracle has been telling everyone Oracle SID != SERVICE_NAME.
Java Developers have always smiled and nodded with this statement as the oracle thin client jdbc url would always be something like:
jdbc:oracle:thin:@server:1521:SERVICE_NAME, or
jdbc:oracle:thin:@server:1521:SID
which would work because databases always either had the SID and SERVICE_NAME as the same, or there was some magic that the Oracle listeners did to make this work.
Somewhere in the past couple of releases of Oracle this transparent mapping has ceased to exist. In particular Oracle 10G XE out of the box doesn’t support this sort of option.
It seems the new preferred way of doing Oracle JDBC URLs is:
jdbc:oracle:thin:@server:1521/SERVICE_NAME.
I discovered this information while working with making a WebSphere Portal instance use Oracle as it’s data repository. The documentation for doing this suggested using the old school SID style of url. I tried this first as it felt familiar, but things didn't work. Changing the url to use the PORT/SERVICE_NAME approach looked to mostly work, but didn’t quite spread the changes to everywhere required. I needed to run WPSConfig connect-database to update all the hidden bits of configuration around the portal installation. Unfortunately, this failed as the wpsconfig script calls out to programs that expect the url to follow the old school syntax (the stack trace show an index-out-of-bounds exception while parsing connect srings). I then had to go in and update the configuration on the database to enable connection via the old SID style approach.
This was done by working on the listener.ora file
(C:\oraclexe\app\oracle\product\10.2.0\server\NETWORK\ADMIN) to add in a SID_LIST_LISTENER entry for the database.
This was simply to add:
(SID_DESC =
(GLOBAL_NAME = XE)
(SID_NAME = XE)
(ORACLE_HOME = C:\oraclexe\app\oracle\product\10.2.0\server)
)
to the SID_LIST of the SID_LIST_LISTENER
I ended up with a SID_LIST_LISTENER that looks like this:
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = PLSExtProc)
(ORACLE_HOME = C:\oraclexe\app\oracle\product\10.2.0\server)
(PROGRAM = extproc)
)
(SID_DESC =
(SID_NAME = CLRExtProc)
(ORACLE_HOME = C:\oraclexe\app\oracle\product\10.2.0\server)
(PROGRAM = extproc)
)
(SID_DESC =
(GLOBAL_NAME = XE)
(SID_NAME = XE)
(ORACLE_HOME = C:\oraclexe\app\oracle\product\10.2.0\server)
)
)
This configuration works with an out of the box Oracle XE installation, and lets me use the jdbc:oracle:thin:@server:1521:SID.
Related posts:
[...] Ensure that the database is accessible using the Oracle SID (see the early post Oracle SID!=Service name) [...]
Correction, instead of:
jdbc:oracle:thin:@server:1521/SERVICE_NAME
It should be:
jdbc:oracle:thin:@//server:1521/SERVICE_NAME
(Added a ‘//’ between the ‘@’ and ‘server’)
You saved me a lot of time. I was suffering configuring WebSphere Oracle data source using service_name.
jdbc:oracle:thin:@111.111.111.111:1526/service_name works for me.
Thanks,
Oleg
Thx a lot man !
I lost many time trying to solve my probleme of communication between JDBC and OracleDB when I jsut have to change a “:” in “/”.
1000thx man
Great help, thank you very much, its working for me……
Hi, This is a wonderful article. I wish I had seen this first. You are absolutely right, works great for me.
Thanks! That was helpful.
Thanks too!
In Java code, you can use high level method calls rather than DriverManager old style.
2
3
4
5
6
7
8
9
10
11
12
13
// ...
OracleDataSource ds = new OracleConnectionPoolDataSource();
ds.setDriverType("thin");
ds.setServerName(...);
ds.setPortNumber(...);
ds.setServiceName(...);
ds.setUser(...);
ds.setPassword(...);
ds.setDescription(...);
Connection myConnection = ds.getConnection();
Thanks
soapui does not have service field
but connection string you’ve posted for service works perfect!