Site icon R-bloggers

Connecting R to an Oracle database with RJDBC

Michael J Bommarito II

[This article was first published on Bommarito Consulting » r, and kindly contributed to R-bloggers]. (You can report issue about the content on this page here)
Want to share your content on R-bloggers? click here if you have a blog, or here if you don't.

In many circumstances, you might want to connect R directly to a database to store and retrieve data.  If the source database is an Oracle database, you have a number of options:

  Using ROracle should theoretically provide you with the best performing client, as this library is a wrapper around the Oracle OCI driver.  The OCI driver, however, is platform-specific and requires you to install Oracle database client software.

   Using RODBC for Oracle is like using an ODBC connection for any database; so long as your platform provides an ODBC manager and drivers, you are OK.  On Linux, this means unixODBC, and on Windows, this means the Oracle Data Access Components package.

What if you don’t want to write code that is either platform-specific or requires relatively complex, platform-specific installation steps?  In this case, you should consider using RJDBC.

  I’ll assume that you have a JRE/JDK installed and know the path to your JAVA_HOME.

  The first step is to obtain the Oracle JDBC drivers, e.g., the 11gR2 release drivers.  You can pick the lowest compatible Java version you’d like to support; I’m using ojdbc6.jar, which should support Java 6+.
  Next, make sure you know how to connect to your source database.  You’ll need the following information for your database listener:
  • Hostname or IP, e.g., database.company.com
  • Port, e.g., 1521
  • Service name or SID, e.g., ORCL
  • Username
  • Password

This information will allow us to construct the DSN, which will look something like this:  jdbc:oracle:thin:@//hostname:port/service_name_or_sid

Armed with this DSN and your Java home, you should now be able to modify and execute the example below.

To leave a comment for the author, please follow the link and comment on their blog: Bommarito Consulting » r.
R-bloggers.com offers daily e-mail updates about R news and tutorials about learning R and many other topics. Click here if you're looking to post or find an R/data-science job.
Want to share your content on R-bloggers? click here if you have a blog, or here if you don't.