ROracle installation on Linux

ROracle is an open-source R package supporting a DBI-compliant Oracle driver based on the high-performance OCI library. ROracle is publicly available on the Comprehensive R Archive Network (CRAN) and is maintained by Oracle. It enables highly scalable and performant connectivity to Oracle Database for data transfer, along with enabling transaction-level control and execution of user-provided SQL statements.

This article is to make it easy to install ROracle on Linux.

Pre-requisites for ROracle

• R should be installed

sudo yum install R.x86_64 -y

• Oracle client should be installed along with SDK

yum localinstall oracle-instantclient19.6-basic-19.6.0.0.0-1.x86_64.rp 
yum localinstall oracle-instantclient19.6-devel-19.6.0.0.0-1.x86_64.rpm 
export LD_LIBRARY_PATH=/usr/lib/oracle/19.6/client64/lib:$LD_LIBRARY_PATH

• JDK is installed and JAVA_HOME set

export JAVA_HOME=/u01/oracle/jdk1.8.0_211

• Re-configure R with JAVA_HOME

R CMD javareconf -e JAVA_HOME=/u01/oracle/jdk1.8.0_211

• Install the DBI package from CRAN and install

wget https://cran.r-project.org/src/contrib/DBI_1.1.2.tar.gz 
R CMD INSTALL DBI_1.1.2.tar.gz

Download and Install ROracle

ROracle is available on the R CRAN site here. Download the ROracle tar file.

wget https://cran.r-project.org/src/contrib/ROracle_1.3-1.1.tar.gz

Install the ROracle package by specifying the Oracle Client and the SDK location

R CMD INSTALL --configure-args='--with-oci-lib=/usr/lib/oracle/19.6/client64/lib --with-oci-inc=/usr/include/oracle/19.6/client64' ROracle_1.3-1.1.tar.gz

Sample R Code connecting to ADW

The below code connects to ADW using the ROracle package and exports the output as a CSV file.

Before running the R code set the TNS_ADMIN to the folder containing the wallet file and update the sqlnet.ora to reflect the wallet directory.

library(ROracle)
drv <- dbDriver("Oracle") 
connect.string <- '(description= (retry_count=20)(retry_delay=3)(address=(protocol=tcps)(port=1522)(host=adb.eu-frankfurt-1.oraclecloud.com))(connect_data(service_name=someadw_low.adb.oraclecloud.com))(security=(ssl_server_cert_dn="CN=adwc.eucom-central-1.oraclecloud.com, OU=Oracle BMCS FRANKFURT, O=Oracle Corporation, L=Redwood City, ST=California, C=US")))' 
con <- dbConnect(drv, username ="ADMIN", password="somepassword",dbname = connect.string) 
rs <- dbSendQuery(con, "select * from SOME_TABLE") 
data <- fetch(rs) 
write.csv(data,"/home/opc/data.csv"))

Originally published at https://www.linkedin.com.