Connect to Oracle Database from R on mac

This is a follow-up post of this Connect to Oracle from R by Ankit Agarwal. I tried to add a comment to his blog, but the formatting was all messed up, so thought of writing it up here more for my own reference.

Just adding how to connect to Oracle DB from R on a mac. I am on El Capitan version 10.11.3.

We will use RJDBC here. First part is to figure out how to find and set JAVA_HOME.

Run this command from command line:

ls -l $(which java)

If this doesn’t output anything, then you need to install Java. Go to the Java SDK download page and download the dmg file for Mac for the latest Java SDK. Then install Java by double clicking the dmg file.

In my case, this outputs:

lrwxr-xr-x  1 root  wheel  74 Jan 13  2016 /usr/bin/java -> /System/Library/Frameworks/JavaVM.framework/Versions/Current/Commands/java

Please scroll right to see the symbolic link. You want to note down the part up to JavaVM.framework to use in R script i.e. /System/Library/Frameworks/JavaVM.framework in my case. This will be set to JAVA_HOME in our script. (You can also set the environment variable JAVA_HOME using .bashrc if you want.)

Download ojdbc JAR as mentioned in the main post. I just downloaded ojdbc6.jar and stored it in my home directory.

That’s all you need to install.

Then try the following from R, which is just the code given in the main post with slight modifications:

# install RJDBC if not done already
install.packages("RJDBC")

# Set JAVA_HOME to what we found out before
jHome <- '/System/Library/Frameworks/JavaVM.framework'
Sys.setenv(JAVA_HOME=jHome)
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="~/ojdbc6.jar")

# db connection details; please fill in yours
dbHost  <- "xx"
dbPort  <- "xx"
srvName <- "xx"
dbUser  <- "xx"
dbPass  <- "xx"

conStr <- paste("jdbc:oracle:thin:@//", dbHost, ":", dbPort, 
                "/", srvName, sep = "")
jdbcConnection <- dbConnect(jdbcDriver, conStr, 
                            dbUser, dbPass)

# Query to get count of a table; replace YOUR_TABLE
dbGetQuery(jdbcConnection, "select count(*) 
           from <YOUR_TABLE>")

# Close connection
dbDisconnect(jdbcConnection)
Print Friendly, PDF & Email

Leave a Reply

Your email address will not be published. Required fields are marked *