How to install ROracle on Windows 10
For data analysis, it is convenient to have a big dataset. To handle data efficiently, it is best if the data remains inside the database. This article shows how you can set up RStudio together with ROracle and your Oracle Autonomous Database.
To start with your installation, you have to know your local architecture — Either 32- or 64-bit. This guide uses the 64-bit installation files.
👩💻 Instant client and SDK
First, download the basic oracle instant client that fits your architecture.
You will also need to download the instant client SDK that matches your basic instant client version. Here we use version 19.6.0.
📁Extract both downloaded ZIP files and put the SDK directory from the instant client SDK in a subdirectory of the extracted basic instant client directory at the same level as the vc14 folder. A screenshot of the target structure is shown below. ⏬
Copy the assembled instant client directory to C:\instantclient or to another path you prefer.
🔨 RTools
Download and install RTools.
👛 Wallet
Download your wallet, unzip it and copy it inside the instant client or to another path you prefer.
C:\instantclient\wallet\whaleDB
🌍 Environment Variables
🧙♂️Set OCI_LIB depending on your architecture of 64- or 32-bit.
OCI_LIB64 = C:\instantclient
OCI_LIB32 = C:\instantclient🧙♂️Set OCI_INC to the SDK-include folder and ORACLE_HOME to your instantclient if you don’t have another local Oracle installation.
OCI_INC = C:\instantclient\sdk\include
ORACLE_HOME = C:\instantclient🧙♂️Set TNS_ADMIN to the location of your extracted wallet.
TNS_ADMIN = C:\instantclient\wallet\whaleDB🧙♂️Set RTOOLS40_HOME to the location of your RTools installation.
RTOOLS40_HOME = C:\rtools40🧙♂️Set the PATH variable to include the location of oci.dll what is usually on the top level inside the instant client. For this example the path would be C:\instantclient .
🎬 RStudio
Now download and install RStudio. In this setup, the free version of RStudio Desktop 1.3.959 was used.
When you start RStudio you can check with the following commands, if the environment variables are set correctly. ⏬
R.home(component = "home")
Sys.getenv("PATH")
Sys.getenv("OCI_INC")
Sys.getenv("OCI_LIB64")
Sys.getenv("ORACLE_HOME")
Sys.getenv("TNS_ADMIN")If you have to change the environment variables, you need to restart RStudio before the changes are in effect. 💨
🧮 R
Now install version 3.6.0 of R. Not all of the versions of R work with the ROracle versions. The compatible versions are listed at the ROracle download page under every ROracle version (see screenshot below).
📄 ROracle
Download the ZIP file with version 1.3–2 of ROracle that is compatible with version 3.6.0 of R.
To install ROracle go into RStudio and execute the following commands.
💬 First set the current working directory of RStudio to the path where your ROracle ZIP file is stored.
setwd("C:/Users/me/Downloads")
install.packages("ROracle_1.3-2.zip",repos = NULL)
install.packages("DBI")
library("DBI")
library("ROracle")After changing to the right directory, install the ROracle package. If everything is set up correctly, you should see the following output in RStudio.
🔁 Connect to your Cloud Database
To check if you have all the right credentials, check your database connection with SQLDeveloper or another IDE.
📝Edit the sqlnet.ora file to contain your wallet location.
WALLET_LOCATION = (SOURCE = (METHOD = file) (METHOD_DATA = (DIRECTORY="C:\instantclient\wallet\whaleDB")))
SSL_SERVER_DN_MATCH=yes📋 Go to your tnsnames.ora file inside your wallet and copy the connection string that you want to use (or the one with the _high ending) and replace the string between the ' 'in the following query:
connect.string <- '(description= (retry_count=20)(retry_delay=3)(address=(protocol=tcps)(port=1522)(host=adb.eu-zurich-1.oraclecloud.com))(connect_data=(service_name=ftbnmxtf0r98h3q_whaledb_high.adwc.oraclecloud.com))(security=(ssl_server_cert_dn="CN=adb.eu-zurich-1.oraclecloud.com,OU=Oracle ADB ZURICH,O=Oracle Corporation,L=Redwood City,ST=California,C=US")))'A second (simpler) solution would be to use the connection string alias from your tnsnames.ora.
connect.string <- 'whaledb_high'🔀 Open RStudio and enter the following commands to setup the connection.
library(DBI)
library(ROracle)
drv <- dbDriver("Oracle")
connect.string <- 'whaledb_high'❔Then connect to your oracle database with the dbConnect command.
con <- dbConnect(drv, username ="admin", password="YourSecretPassword.",dbname = connect.string)❓ Create a simple query to test if the connection works:
rs <- dbSendQuery(con, "select * from tbl_wine")
data <- fetch(rs)
View(data)If you don’t have any data inside your database yet, you can test the connection also with dbReadTable.
dbReadTable(con, 'DUAL')
If you have problems executing ROracle commands, try to run RStudio as administrator.
Summary
➡️ Installing ROracle and setting up the local environment to work with the cloud database in RStudion wasn’t easy. 💬 I hope this installation guide helps others to save a lot of time when they want to get startet with R and the Oracle Database.
Please support this article with your claps👏👏👏 to help it spread to a broader audience. 💭 Don’t hesitate to ping me if you have any thoughts or questions on the subject! I’m on Twitter → @jasminfluri
