R Programming Language Connectivity to Oracle.
R is an open source programming language and software environment for statistical computing and graphics. It is a fully functional programming language, widely used by statisticians to perform data analysis. It can also be a neat tool for Oracle DBA’s to graph and analyse database performance metrics. If you intend to embark on developing a sizable R+Oracle project, i’d encourage you to use Oracle Enterprise R and/or the Oracle Advanced Analytics.
Below are the steps on how to install and configure the R language on Ubuntu Linux with connectivity to Oracle.
These steps assume that you have an already installed and running Oracle 11gR2 database.
The high level steps are as follows
1) Install the R programming language environment
2) Download and install the oracle instant client
3) Download and install the following R packages
4) Start using R with Oracle.
Install the R programming language environment
Refer to the installation instructions at www.r-project.org for your platform.
If you are installing this on Ubuntu Linux (As I have on Ubuntu 12.10), open the “Ubuntu Software Center” and install the following packages.
Download and install the oracle instant Client
As your regular o/s user, download and install (Installation is nothing other than unzipping the downloaded file) the oracle instant client.
Download The instant client for your o/s platform from http://www.oracle.com/technetwork/database/features/instant-client/index-097480.html.
You need to download
– Instant Client package – Basic
– Instant Client package – SDK
For the purpose of this installation, we are going to assume that the instant client has been installed into /u01/Rk/Apps/oracle/instantclient_11_2.
Download and install the R packages
– Download DBI from http://cran.r-project.org/web/packages/DBI/index.html. (Download the package source)
– sudo su –
– cd <To the directory where DBI_0.2-5.tar.gz>
root# R CMD INSTALL DBI_0.2-5.tar.gz * installing to library ‘/usr/local/lib/R/site-library’ * installing *source* package ‘DBI’ ... ** R ** inst ** preparing package for lazy loading Creating a generic function for ‘summary’ from package ‘base’ in package ‘DBI’ ** help *** installing help indices ** building package indices ** installing vignettes ‘DBI.Rnw’ ** testing if installed package can be loaded * DONE (DBI)
– Download the ROracle source from http://cran.r-project.org/web/packages/ROracle/index.html
– sudo su –
– Set the following environment variables
root# export OCI_LIB=/u01/Rk/Apps/oracle/instantclient_11_2 root# export LD_LIBRARY_PATH=/u01/Rk/Apps/oracle/instantclient_11_2:$LD_LIBRARY_PATH
root# R CMD INSTALL ROracle_1.1-5.tar.gz * installing to library ‘/usr/local/lib/R/site-library’ * installing *source* package ‘ROracle’ ... ** package ‘ROracle’ successfully unpacked and MD5 sums checked configure: creating ./config.status config.status: creating src/Makevars ** libs gcc -std=gnu99 -I/usr/share/R/include -DNDEBUG -I/u01/Rk/Apps/oracle/instantclient_11_2/sdk/include -fpic -O2 -pipe -g -c rodbi.c -o rodbi.o gcc -std=gnu99 -I/usr/share/R/include -DNDEBUG -I/u01/Rk/Apps/oracle/instantclient_11_2/sdk/include -fpic -O2 -pipe -g -c rooci.c -o rooci.o gcc -std=gnu99 -shared -o ROracle.so rodbi.o rooci.o -L/u01/Rk/Apps/oracle/instantclient_11_2 -lclntsh -L/usr/lib/R/lib -lR installing to /usr/local/lib/R/site-library/ROracle/libs ** R ** inst ** preparing package for lazy loading ** help *** installing help indices ** building package indices ** installing vignettes ** testing if installed package can be loaded * DONE (ROracle)
Using The R Language with Oracle
Now you are ready to Run your first R program, Run a query against the database, and plot the output on a graph.
Invoke the R language command line by typing in the following
From the R command line use the following commands. (The formatting is a bit messed up, click on “view code” to see the actual commands)
> library(ROracle) > drv <- dbDriver("Oracle") > con <- dbConnect(drv,username="sh",password="sh",dbname="burl5vb1:1521/rk01") > res <- dbSendQuery(con,"select time_id,sum(quantity_sold) from sales + where time_id > to_date('20-DEC-2001','DD-MON-RR') + group by time_id") > data <- fetch(res) > data TIME_ID SUM(QUANTITY_SOLD) 1 2001-12-20 23:00:00 473 2 2001-12-21 23:00:00 374 3 2001-12-22 23:00:00 1034 4 2001-12-23 23:00:00 1662 5 2001-12-24 23:00:00 470 6 2001-12-25 23:00:00 289 7 2001-12-26 23:00:00 1076 8 2001-12-27 23:00:00 1196 9 2001-12-28 23:00:00 232 10 2001-12-29 23:00:00 758 11 2001-12-30 23:00:00 786 > plot(data)
You will see a plot like the one below
Happy R scripting.
If you want to learn the R Language, i would recommend the book The Art of R programming.