There are two forms of JDBC URLs:

Old form: jdbc:oracle:thin:<host>:<port>:<SID>

New form: jdbc:oracle:thin://<host>:<port>/<SERVICE_NAME>

The switch between old and new forms apparently happened at 9iR2,

If you see "foo" (or "FOO") it's a SID, if you see "foo.mydom.net" it's a SERVICE_NAME". Many people relate them and think that SERVICE_NAME = SID + DOMAIN. But not necessarily so.

The answer lies in the server configuration. If the listener.ora file is minimal, it will mediate between the database instance that registers and the clients that connect to port 1521 (or whatever). But that ONLY works with SERVICE_NAME, and that ONLY works with the new style URL and that's only supported by the JDBC driver (even though the filename is NOT different. Be aware that ojdbc14.jar may be old or new, you can only tell from the slightly larger size (1.5 instead of 1.3 or 1.4 MB).

The fix is to add a SID_LIST to the listener.ora file on the server:

SID_LIST_LISTENER = (SID_LIST =
(SID_DESC =(GLOBAL_DBNAME = foo.mydom.net)
(ORACLE_HOME = /usr/oracle/oraclehome)
(SID_NAME = mysidthatilike)
)
)

And note that you can put any SID_NAME of your choosing, it doesn't seem to matter which.

Some of this may be talked about on Metalink in

- Note:76531.1 Service Name Usage in Net8i
- Note: 183905.1 JDBC Thin Connection to Database Fails with ORA-12505/TNS-12505

A final lesser-known feature is that you can also use a full blown connection descriptor on the jdbc URI. For instance:

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

or

url="jdbc:oracle:thin:@(DESCRIPTION=
(ADDRESS=(PROTOCOL=TCP)(HOST=cluster_alias) (PORT=1521))
(CONNECT_DATA=(SERVICE_NAME=service_name)))"