RJDBC to the Rescue
I have been using the statistical language R for some time now to do some of my intensive off-line calculations. The great thing(s) about R is that it’s fast, well documented, extensive, and open source.
However, I regularly run into problems configuring and connecting to Oracle databases, mainly because the API’s are extremely obtuse, the code for these systems are closed, and licences are extremely expensive. Even the ODBC drivers (a universal database connectivity layer) are pretty difficult to find (at least for my OSX platform). The whole process for getting everything working is usually pretty convoluted.
However, the RJDBC package for R simplifies everything by using a Java database connector. Rather than setting up ODBC profiles, downloading drivers, etc. Everything can be handled in R by including the package, setting pointers to the JDBC class package (Oracle class package available for the mac here), and formatting a single connection string, like so:
# point the R package at the appropriate java driver class, as well as the class file
# the “easy” connection string spec for oracle db’s
conn<-dbConnect(drv, “jdbc:oracle:thin:@<host>:<port default=1521>:<SID>”,”username”,”password”)
query = “SELECT * FROM A TABLE”
And that’s it! Maybe this will save someone else some time and headaches. Arguably, the use of an additional Java layer may negatively affect performance in some cases, but in my case it is not noticeable.