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.

# Set JAVA_HOME, set max. memory, and load rJava library
Sys.setenv(JAVA_HOME='/path/to/java_home')
options(java.parameters="-Xmx2g")
library(rJava)
# Output Java version
.jinit()
print(.jcall("java/lang/System", "S", "getProperty", "java.version"))
# Load RJDBC library
library(RJDBC)
# Create connection driver and open connection
jdbcDriver <- JDBC(driverClass="oracle.jdbc.OracleDriver", classPath="lib/ojdbc6.jar")
jdbcConnection <- dbConnect(jdbcDriver, "jdbc:oracle:thin:@//database.hostname.com:port/service_name_or_sid", "username", "password")
# Query on the Oracle instance name.
instanceName <- dbGetQuery(jdbcConnection, "SELECT instance_name FROM v$instance")
print(instanceName)
# Close connection
dbDisconnect(jdbcConnection)
view raw r_oracle_jdbc_example1.R hosted with ❤ by GitHub