Connecting R to an Oracle database with RJDBC

November 22, 2012
By

(This article was first published on Bommarito Consulting » r, and kindly contributed to R-bloggers)

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 his blog: Bommarito Consulting » r.
R-bloggers.com offers daily e-mail updates about R news and tutorials on topics such as: visualization (ggplot2, Boxplots, maps, animation), programming (RStudio, Sweave, LaTeX, SQL, Eclipse, git, hadoop, Web Scraping) statistics (regression, PCA, time series, trading) and more...

If you got this far, why not subscribe for updates from the site? Choose your flavor: e-mail, twitter, RSS, or facebook...

Comments are closed.

Top 7 articles of the week

  1. Top 100 R packages for 2013 (Jan-May)!
  2. A list of R packages, by popularity
  3. Understanding Multicollinearity
  4. Stepping up to Big Data with R and Python: A Mind Map of All the Packages You Will Ever Need
  5. Where is the R Activity?
  6. Select operations on R data frames
  7. Using Metadata to find Paul Revere

Sponsors

Mango solutions
mango-solutions.com
Zero Inflated Models and Generalized Linear Mixed Models with R
Quantide: statistical consulting and training
RStudio homepage
RStudio: a free and open source IDE for R
REvolution analytics
HMS Analytical Software GmbH
http://www.eoda.de
Zero Inflated Models and Generalized Linear Mixed Models with R Zero Inflated Models and Generalized Linear Mixed Models with R. Zuur, Saveliev, Ieno (2012).