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
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)