Using Oracle's OCI Driver With JDBC

I just spent a morning learning how to use the Oracle OCI driver instead of the thin driver, and it's worth recording for future reference. I think it used to be more complicated, and you had to actually install some Oracle app or other, but as of version 10.1.2 or so, the process is somewhat simpler and just requires a couple of downloads. If you've always avoided using OCI to connect from Java or just never had a reason to do it before, here's what you need to do: 1) Download the Oracle "Instant Client", which lets you use OCI. I don't know exactly what these terms really mean in Oracle-speak. Just go to this download page, select your platform, then find your database version and download the "Instant Client Package - Basic" and "Instant Client Package - JDBC Supplement" packages. (You can use the Basic Lite package in place of the first one if you only need English/western i18n stuff.) You'll be prompted to log in to or register for OTN, which is a free registration. 2) Make sure you use the SAME DATABASE VERSION of the JDBC thin driver. If you don't already have this JAR, it comes in the Instant Client zip file (named ojdbc14.jar). You can also get it from this page if you really feel the need. It's important that the versions match, because the thin client uses native calls to access the OCI stuff, and the file names are hardcoded (I suspect) into the thin driver. So if you use version 10.x of the JDBC driver, but version 11 of the OCI driver, you'll get an error like "java.lang.UnsatisfiedLinkError: no ocijdbc10 in java.library.path".

3) Extract the two Instant Client downloads into the same directory somewhere.

4) Create your tnsnames.ora file somewhere (if you don't already have one). This file describes how to connect to one or more Oracle databases to any Oracle software on your computer, such as the Instant Client. It's widely documented on the web, like on this page. If you have a RAC and don't know much about this file (like me before this morning), then someone else probably set up the cluster. Get a tnsnames.ora entry from them and just paste it into a text file somewhere. I just put the tnsnames.ora in the same directory where I extracted the Instant Client.

5) Add/edit two environment variables. First, add the directory from 3) to your library path. In Windows, this means adding the directory to your PATH. For Linux:

export LD_LIBRARY_PATH=<directory>

Then add a TNS_ADMIN variable that points to the directory that contains tnsnames.ora (the same as the library directory if you put it with the rest of the extracted files).

6) Use JDBC to connect like normal with these settings (and a username + password, of course, if applicable):

driverClass=oracle.jdbc.driver.OracleDriver
connectionUrl=jdbc:oracle:oci:@<database alias>

where <database alias> is the alias specified in tnsnames.ora. It's the left hand side of the initial assignment in a TNS entry--"FITZGERALD.SALES" in this example:

FITZGERALD.SALES =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = server1)(PORT = 1521))
    (CONNECT_DATA =
      (SID = ORCL)
    )
  )

That's it! Section 7.4.3 of the Oracle JDBC Developer's Guide has a good rundown of different ways you can use JDBC to connect with OCI once you get it installed. I don't claim that this is the best way to do any of this. I just know it works.