Background ROracle is an add-on package that allows you to easily access an existing Oracle server from the command line of the R statistical environment: you can list existing tables, display their structure, download data – either on a fairly elementary level (like me) or fully SQL-powered.
This is of course very convenient, especially if someone else is taking care of the Oracle server in the background (lucky me). However, installation of ROracle is not totally straightforward, as no pre-compiled binaries are available from CRAN, only the package source: in order to get this to run, you will have to compile the package and link it against an Oracle client yourself. And while it’s not exactly rocket science, and the installation instructions are reasonably clear, there is no way in hell I will be remembering all the steps should I have to re-build at some point in the future. Therefore this post.
Details The recipe below has worked for me on a Windows 7 machine with R 2.15.1 and ROracle 1.1-4
Prerequisites You will need the Rtools matching your R installation (for me Rtools 2.16). If you need to install them, this previous post may be helpful. You will also need the Oracle Instant Client (currently version 11.2), as described in the ROracle installation guide: you need both the basic (or basic lite) and the SDK package (in the 32-bit or 64-bit version or both, as required).
Getting stuff from Oracle The downloads are zero cost, but not free (if they were, presumably ROracle would be distributed with the client source and as a pre-compiled package, right?). If you have already an account with Oracle, go to www.oracle.com and sign in. If you don’t have an account, still go to www.oracle.com and sign up for one (link at the top left): it’s free, they don’t require your firstborn or their personal information, and they are fairly restrained as far as (direct) spamming goes – I think I got more paper letters (ca. 1/year) than emails. Note that for the download to work, you need to be both signed in and have javascript activated.
The Oracle website is about as bad as you would expect from a large commercial entity. Googling instead “Instant client” took me to the current download page at
www.oracle.com/technetwork/database/features/instant-client/index-097480.html
in one go. Once there,
- Select 32-bit MS Windows (top choice). This brings you to the download page where you can select either Instant Client Package – Basic (for full international support) or Basic Lite (English only). Don’t forget to read (and accept) the licence agreement, after which you can save a
.zipfile to your disk. - Repeat the same process for the Instant Client Package SDK, which gives you another
.zipfile. - Unpack both of the
.zipfiles. - Create
c:\instantclient\i386on your disk, and drop the directory you have unzipped from the instantclinet_11_2 basic (or basic lite) package there. - Drop the
sdksubdirectory from the unzipped SDK package into the same place.
If you want install the 64-bit client instead or in addition, repeat Steps 1-5, but choose the 64-bit MS Windows option (of course) and use the subdirectory c:\instantclient\x64.
Building ROracle At the R command line, execute
install.packages("ROracle", type="source")
If that does not return an error message, try
require(ROracle)
and if that does not given an error message, it seems you have built a loadable Oracle client: presumably either 32- or 64-bit, depending on your installation of the client, though in all honesty, I have only tested the 32-bit client so far. If you want to build both, you may have luck using the argument INSTALL_opts="--merge-multiarch" with install.packages, or prefer to consult the installation guide again.
Testing You need to have the service name of your Oracle server (as well as username and password) to connect. Your local Oracle administrator would be a great person to ask, or you may want to look for the tnsnames.ora file on your machine (note that if that file exists, you probably have already some Oracle client software on your machine, and it is quite likely that you wouldn’t have had to jump through all of the hoops above; sorry about that – I’m just a trained monkey who got lucky surfing, not an expert).
Anyhoo, assuming you have all that you need, just continue after require(ROracle) with
ora = Oracle()
con = dbConnect(ora, username = "USER",dbname = "SERVICENAME", password="PASSWORD")
summary(con)
dbListTables(con, "MYSCHEME") ## the scheme you want to work with
dbDisconnect(con)
as seen on ?Oracle. Enjoy!
nugaemeae 