ROracle is an R package that bridges the gap between the R programming language and Oracle Database instances. You can establish connections with Oracle Database and Oracle Autonomous Database, retrieve data, and perform various database operations directly from within R code. This package seamlessly integrates R, a powerful statistical and data analysis programming language, with Oracle Database, a widely adopted enterprise solution for managing, exploring, and analyzing datasets at scale.

Use ROracle to run SQL queries, fetch data from database tables and views, and interact with the database seamlessly within the familiar R environment. This integration empowers you to harness the analytical capabilities of R and Oracle Database while leveraging the robust data storage and management capabilities of Oracle Database, streamlining your workflow and enhancing productivity.

To use ROracle, you need the Oracle Database Instant Client installed on your machine, and the ROracle library must be installed and configured in your R environment. Working with ROracle from your R session is as simple as creating a database connection and then using the ROracle functions to interact with your database data.

# Write the iris data frame to database table IRIS

R> dbWriteTable(con, 'IRIS', iris)

# Check that table IRIS exists

R> dbExistsTable(con,'IRIS')

# Check IRIS table column list

R> dbListFields(con, "IRIS")

# Read the first 5 rows of IRIS table

R> dbGetQuery(con, 'select * from IRIS where rownum < 6')

Sepal.Length Sepal.Width Petal.Length Petal.Width Species
1          5.1         3.5          1.4         0.2  setosa
2          4.9         3.0          1.4         0.2  setosa
3          4.7         3.2          1.3         0.2  setosa
4          4.6         3.1          1.5         0.2  setosa
5          5.0         3.6          1.4         0.2  setosa

# Disconnect from Oracle Database

> dbDisconnect(con)

Steps to build ROracle for your specific R version

Oracle provides pre-compiled ROracle Linux and Windows binaries for some R versions downloadable by users from the Oracle Technology Network. However, users may want to compile ROracle against a different R version. For such versions, the ROracle source package requires compilation before being installed in R. As of this writing, the latest version of ROracle is 1.4.1.

Build and install ROracle on Windows

To install ROracle on Windows, the open source DBI library is required along with RTools, along with the Oracle Instant Client. RTools contains all the compilers needed to build R packages from source on Windows systems.

1.           Install your preferred R version on Windows

2.           Install the Oracle Database Instant Client

3.           Download the ROracle 1.4.1 source tar file

4.           Install RTools 4.3

Note: When prompted to add the Rtools utilities to your PATH environment variable, select YES

5.           Optionally install Microsoft's MikTeX to build the ROracle help files in HTML format

6.           Add the Instant Client paths to the environment variables PATH and OCI_LIB64 or OCI_LIB32, depending on your Windows system’s architecture.

• In Windows Control Panel, choose System, then click Advanced System Settings.
• On the Advanced tab, click Environment Variables.
• Under System Variables, create OCI_LIB64 or OCI_LIB32 to point to the directory containing oci.dll.
• Set the value of OCI_LIB64 or OCI_LIB32 to C:\<instant client path>
• Under System Variables, edit PATH to include C:\<instant client path>

7.          In R, verify that the Oracle Instant Client and RTools paths are correct.

R> Sys.getenv("PATH")

# For 64-bit systems

R> Sys.getenv("OCI_LIB64")

# For 32-bit systems

R> Sys.getenv("OCI_LIB32")
R> Sys.which("make")

8.           Install the DBI and ROracle packages.

R> install.packages("DBI")
R> install.packages("ROracle_1.4-1.zip", type="source", repos = NULL)

Alternatively, build and install the ROracle Windows binary using R CMD INSTALL from the Windows command line:

$ R CMD INSTALL --build  ROracle_1.4-1.tar.gz

The specific steps you follow may vary slightly based on the version of R and RTools you are using. Always check the documentation or instructions provided with the RTools package for any additional or version-specific details. 

Build and install ROracle on Linux

On Linux, only the open source DBI package and Oracle Instant Client are required.

Refer to the Oracle Machine Learning for R Installation Guide for instructions on installing and configuring the Oracle Instant Client.

R> install.packages("DBI")
R> install.packages("ROracle_1.4-1.tar.gz", type="source", repos=NULL)

Alternatively, build and install the ROracle Windows binary using R CMD INSTALL from the Linux command line:

 $ R CMD INSTALL --build  ROracle_1.4-1.tar.gz

For more information…

For more information and examples, refer to the ROracle documentation. Note that in addition to being an interface to Oracle Database from R for general use, ROracle supports database access for Oracle Machine Learning for R. Refer to the OML4R Installation Guide for instructions on installing ROracle for use with OML4R.