Intall ROracle Package for Windows and Mac OS

Since ROracle package comes as a source code, its installation requires compilation. The installation is further complicated due to requiring Oracle client. Also, for Mac OS, fix_oralib.rb has been obsolete since Oracle Instant client 12.1 for OS X was released, we will get many errors related to loading dylib files from Oracle client:

1. OCI Libraries not found
2. Library not loaded: @rpath/libclntsh.dylib.12.1 Referenced from: /Library/Frameworks/R.frameworks/...
3. Other errors with Xcode

There is no single correct instruction on how to get the package installed on latest Mac OS systems such as Yosemite and El Capitan. The following documents working instructions for both Windows and Mac OS.

Shortcut Procedure to Install ROracle for Windows (Updated on 9/8/2024)

If you want a customized installation, please follow the procedure below that I proposed on 6/21/2022. Otherwise, this is a much simpler procedure to install ROracle:

1. Install the current version of R-core, such as R 4.4.
2. Install R Studio from https://www.rstudio.com/products/rstudio/download/#download
3. Install Rtools from https://cran.r-project.org/bin/windows/Rtools/ (make sure to download the version such as 4.4 that matches your R version, download the Windows Installer, and choose default settings during the installation)
4. Download the archive file ROracle.zip from your Microsoft Teams File folder. The zip file contains a folder called "instantclient" and individual three files. You need to extract the folder "instantclient" to C drive so that your Oracle 23c client will be in c:\instantclient. Extract these two files, environmentVars.bat and ROracle_1.4-1.tar.gz to your desktop
5. Right mouse click on environmentVars.bat file and choose run it as Administrator (Note: this code is to create an environment variable called OCI_LIB64 and add a path variable to PATH so that they are pointing to c:\instantclient). This will change your registry items for required environment variables.
6. Now open R and run the following three commands one by one and make sure you navigate to the desktop and choose ROracle_1.4-1.tar.gz to continue after the third command:

        install.packages("DBI")

        library(DBI)

        install.packages(file.choose(), repos=NULL, type="source")

To test Oracle connection 

Run the following two commands in R:

• library(ROracle)
• c.string="ism.uakron.edu:1521/cob19c.uanet.edu"
• con=dbConnect(Oracle(),username="scott",password="tiger",dbname=c.string)
• dbListTables(con)

Custom Installation of ROracle for Windows (Updated on 6/21/2022)

The installation for Windows is eventless, but make sure you follow the step carefully:

Install Instant Client

1. Download Oracle Instant Client 64-bit for Windows, including basic package, sql*plus package, and SDK package from https://www.oracle.com/database/technologies/instant-client/winx64-64-downloads.html
2. Unzip all the basic package zip files to a folder, say c:\instantclient, and then unzip sql*plus and SDK packages into the same folder. Create a folder called Network inside c:\instantclient. In the end, the c:\instantclient folder should look like the following after unzipping the three compressed files 

Setup OCI_LIB64 and Path Environment Variables

Follow the instruction at https://www.ecourse.org/news.asp?which=5822 on how to setup Windows environment variables. 

1. Go to Control Panel -> System -> Advanced System Settings -> Environment Variables, and add C:\instantclient to the PATH variable
2. Add a new environment variable OCI_LIB64 that has the value as c:\instantclient
3. Logout Windows and re-login to make the PATH effective

4. Open CMD prompt and test it out with the sqlplus command:

       sqlplus scott@//ism.uakron.edu:1521/cob19c.uanet.edu

   If you can connect with that, then you're good to go.

5.  (optional) Create tnsnames.ora file and save it into c:\instantclient\network\admin folder with the following content:

   ecourse.org =
   (DESCRIPTION =
   (ADDRESS_LIST =
   (ADDRESS = (PROTOCOL = TCP)(HOST = ecourse.org)(PORT = 1521))
   )
   (CONNECT_DATA =
   (SERVICE_NAME = ol8.ecourse.org)
   )
   )

   COB19C =
   (DESCRIPTION =
   (ADDRESS_LIST =
   (ADDRESS = (PROTOCOL = TCP)(HOST =ism.uakron.edu)(PORT = 1521))
   )
   (CONNECT_DATA =
   (SERVICE_NAME = cob19c.uanet.edu)
   )
   )
   
   cob19c.uakron.edu =
   (DESCRIPTION =
   (ADDRESS_LIST =
   (ADDRESS = (PROTOCOL = TCP)(HOST = ism.uakron.edu)(PORT = 1521))
   )
   (CONNECT_DATA =
   (SERVICE_NAME = cob19c.uanet.edu)
   )

    

Install R and R Studio

1. Install the current version of R-core, such as R 4.4. If you want to download an earlier version, go to https://cran.r-project.org/bin/windows/base/old/3.6.0/. Run the downloaded file to install. 
2. Install R Studio from https://www.rstudio.com/products/rstudio/download/#download
3. Install Rtools from https://cran.r-project.org/bin/windows/Rtools/

Install ROracle

1. Download ROracle_1.4-1.tar.gz (for R 4.0 version) from https://www.oracle.com/database/technologies/roracle-downloads.html (the file can be also found here)
2. Open R or R Studio and run command: install.packages("DBI") and then load the package using command library(DBI)
3. Run command: install.packages(file.choose(), repos=NULL, type="source") to choose the downloaded zip file to install.
4. Run command: library(ROracle)

To test Oracle connection 

Run the following two commands in R:

• c.string="ism.uakron.edu:1521/cob19c.uanet.edu"
• con=dbConnect(Oracle(),username="scott",password="tiger",dbname=c.string)
• dbListTables(con)

ROracle for Mac OS (Updated on 6/21/2022)

Follow the instructions carefully. First you will need to get Oracle Instant Client successfully.

Prerequisite: install Xcode command line tools using the command in. terminal:

xcode-select –install

Install Oracle Instant Client

sudo mkdir /usr/local/oracle
sudo mv ~/Downloads/instantclient /usr/local/oracle/instantclient

sudo nano /etc/profile

ORACLE_HOME="/usr/local/oracle/instantclient"
export ORACLE_HOME
DYLD_LIBRARY_PATH=$ORACLE_HOME:$DYLD_LIBRARY_PATH
export DYLD_LIBRARY_PATH
PATH=$ORACLE_HOME:$PATH
export PATH

    source /etc/profile

    sqlplus scott@//cobismcourses.uakron.edu:1521/cob19c.uanet.edu

Install ROracle

1.  Open terminal and run the following commands to copy Oracle's library files 

sudo cp /usr/local/oracle/instantclient/*.dylib.19.1 /usr/local/lib
sudo cp /usr/local/oracle/instantclient/*.dylib /usr/local/lib

sudo cp /usr/local/oracle/instantclient/*.dylib.19.1 /Library/Frameworks/R.framework/Resources/lib
sudo cp /usr/local/oracle/instantclient/*.dylib /Library/Frameworks/R.framework/Resources/lib

2.  Download ROracle package file ROracle_1.3-1.1.tar.zip from https://cran.r-project.org/web/packages/ROracle/index.html and save it anywhere, say Downloads folder

3. Open R, and run command: install.packages("DBI")

4. Go back to terminal, run the following commands to install ROracle

sudo R CMD INSTALL --configure-args='--with-oci-lib=/usr/local/oracle/instantclient --with-oci-inc=/usr/local/oracle/instantclient/sdk/include' ~/Downloads/ROracle_1.3-1.1.tar

5.  Now go to R and run command: library(ROracle)