The ROracle library exposes a powerful collection of methods intended to simplify the exchange of data and other resources across the R-Oracle interface. In particular, R data.frames and/or data.tables can be uploaded to Oracle without first having to explicitly generate DDL: Simply pass the necessary login credentials, name of the table to be created and a data.frame to dbWriteTable, and ROracle takes care of the rest.

Querying existing Oracle table data is even more straightforward: To query a table as is without filtering conditions, pass 1) authentication details in the form of a connection object, and 2) the name of the table in question to dbReadTable. The dataset will be and returned as a data.frame. If a WHERE clause is necessary, instead pass the same connection object along with a query string to dbGetQuery.

What follows are call signatures for some of the more commonly used methods of the OraConnection class (trailing ... represents additional method arguments. See the ROracle documentation for the full specification):

dbGetQuery    signature(conn="OraConnection", statement="character", ... )
dbReadTable   signature(conn="OraConnection", name="character")
dbWriteTable  signature(conn="OraConnection", name="character", value="data.frame")
dbExistsTable signature(conn="OraConnection", name="character")
dbRemoveTable signature(conn="OraConnection", name="character")

For the following examples, assume we’ll be interacting with SAMPLE_TABLE located in SAMPLE_SCHEMA from the SAMPLE_DB database:

library(ROracle)

tablename <- "SAMPLE_TABLE"
schema    <- "SAMPLE_SCHEMA"
password  <- "PASSWORD"
db        <- "SAMPLE_DB"
oradrv    <- dbDriver("Oracle")   # Creates an Oracle Database instance

# pass authentication details to `dbConnect` =>
conn <- dbConnect(drv=oradrv, 
                  username=schema, 
                  password=password, 
                  dbname=db)

An Oracle database instance has been created and a database connection setup. All that’s left to do is pass the connection and tablename along to dbReadTable, and the dataset will be returned to R as a data.frame:

df <- dbReadTable(conn=conn, name=tablename)

print(class(df))    # returns `data.frame`
print(nrow(df))     # returns number of rows in df
print(ncol(df))     # returns number of cols in df

A query string can be passed to dbGetQuery to handle situations in which the dataset first requires filtering prior to returning to R (unlike dbGetTable which returns all table records):

# using the same connection created earlier =>
SQLstr <- "SELECT * FROM SAMPLE_TABLE WHERE FIELD_1='Y'"
df_2   <- dbGetQuery(conn=conn, statement=SQLstr)

print(class(df_2))      # returns `data.frame`
print(nrow(df_2))       # returns number of rows in df
print(ncol(df_2))       # returns number of cols in df

R data.frames can be loaded into Oracle tables via dbWriteTable:

# using the same connection created earlier, and assuming `df2export` is a
# data.frame with all(ncol(df2export)>0, nrow(df2export)>0)) = TRUE =>
newtablename <- "NEW_SAMPLE_TABLE"

result <- dbWriteTable(conn=conn,
                       name=newtablename,
                       value=df2export)

When finished, terminate the database connection (dbDisconnect) and remove the communication link between Oracle and the R client (dbUnloadDriver):

dbDisconnect(conn)

# verify that `dbListConnections` is empty =>
dbListConnections(oradrv)

# remove communication link between database and R client =>
dbUnloadDriver(oradrv)

Wrapping ROracle Methods: Working with Closures

Closures get their name because they enclose the environment of the parent function and can access all its variables (check out this section from Hadley Wickham’s Advanced R for an excellent introduction to closures and their application). We’re going to use R’s closure abstraction to create a wrapper around dbReadTable and dbWriteTable from the OraConnection class that will allow writing/retrieving data to/from Oracle without having to pass a connection object at each invocation. The outer function handles authentication details, while the inner function takes the connection object and passes it to the wrapped OraConnection class method, along with either a tablename (in the case of dbReadTable) or a tablename and data.frame/data.table (in the case of dbWriteTable).

Exporting Datasets from Oracle to R

First we’ll create the retrevial closure, which wraps dbReadTable. The same connection is used as before, repeated here for convenience:

library(ROracle)

tablename <- "SAMPLE_TABLE"
schema    <- "SAMPLE_SCHEMA"
password  <- "PASSWORD"
db        <- "SAMPLE_DB"
oradrv    <- dbDriver("Oracle")   # Creates an Oracle Database instance

# pass authentication details to `dbConnect` =>
conn <- dbConnect(drv=oradrv,
                  username=schema,
                  password=password,
                  dbname=db)

# `dfGetTableAuth`: simple dbReadTable wrapper => 
dfGetTableAuth <- function(connection) {
    conn <- connection
    function(tablename) {
        df <- as.data.frame(dbReadTable(conn, tablename))
        return(df)
    }
}

dfGetTableAuth takes a single argument: An ROracle connection object. After dfGetTableAuth initialization, it will no longer be necessary to pass the connection along with a tablename or SQLstr at each dbReadTable invocation:

# Authenticate to `SAMPLE_SCHEMA@SAMPLE_DB` =>
dfGetTable <- dfGetTableAuth(connection=conn)    # only pass `conn` once


# Verify that dfGetTable's argument is `tablename` =>
print(formals(dfGetTable))    # prints 'tablename'


# Query `TABLE_1`, `TABLE_2`, `TABLE_3` & `TABLE_4` from SAMPLE_SCHEMA@SAMPLE_DB
# and return the dataset as an R data.frame. 
# (It is not necessary to pass "tablename=<tablename>" as a key-value pair. Passing 
# the tablename alone as a string will suffice. In this example, the `tablename` keyword is 
# provided for clarity) =>
table_1 <- dfGetTable(tablename="TABLE_1")
table_2 <- dfGetTable(tablename="TABLE_2")
table_3 <- dfGetTable(tablename="TABLE_3")
table_4 <- dfGetTable(tablename="TABLE_4")

As demonstrated in the standard usage examples of dbReadTable and dbGetQuery, we can replace tablename with SQLstr and dbReadTable with dbGetQuery in dfGetTableAuth. This will transform our closure from one that takes a connection object followed by a tablename, to one that takes a connection object followed by any valid SQL SELECT-stamement, rendering the construct much more generally applicable:

# Assume same connection as above  

# `dfGetSQLAuth`: simple dbGetQuery wrapper => 
dfGetSQLAuth <- function(connection) {
    conn <- connection
    function(SQLstr) {
        df <- as.data.frame(dbGetQuery(conn, SQLstr))
        return(df)
    }
}

# Authenticate to `SAMPLE_SCHEMA@SAMPLE_DB` =>
dfGetSQL <- dfGetSQLAuth(connection=conn)

# Verify that dfGetSQL's argument is `SQLstr` =>
print(formals(dfGetTable))    # prints 'SQLstr'

SQL_1 <- "SELECT * FROM TABLE_1 WHERE FIELD_1='Y'"
SQL_2 <- "SELECT * FROM TABLE_2 WHERE FIELD_1='N'"
SQL_3 <- "SELECT * FROM TABLE_3 WHERE FIELD_1='Y'"
SQL_4 <- "SELECT * FROM TABLE_4 WHERE FIELD_1='N'"

result_1 <- dfGetSQL(SQLstr=SQL_1)
result_2 <- dfGetSQL(SQLstr=SQL_2)
result_3 <- dfGetSQL(SQLstr=SQL_3)
result_4 <- dfGetSQL(SQLstr=SQL_4)   # Each result is a data.frame

Importing Datasets to Oracle From R

Similiar to the previous wrapping of dbReadTable and dbGetQuery, dbWriteTable can be wrapped, resulting in a closure initially requiring a connection object, followed by a tablename and an R data.frame/data.table to load into a database table. Assuming the same connection as before, we want to import the R data.frame df2export into SAMPLE_SCHEMA, and want to name it DATA_FROM_R. First, we’ll define our closure:

# `dfPutTableAuth`: simple dbGetQuery wrapper => 
dfPutTableAuth <- function(connection) {
    conn <- connection
    function(df, tablename) {
        if (dbExistsTable(conn, tablename)) {
            dbRemoveTable(conn, tablename, purge=TRUE)
        }
        retcode=dbWriteTable(conn=conn, 
                             name=tablename, 
                             value=df)       
        return(ifelse(retcode==TRUE,0,1))
    }
}


# Authenticate to `SAMPLE_SCHEMA@SAMPLE_DB` =>
dfPutTable <- dfPutTableAuth(connection=conn)

# Verify that dfPutTable's arguments are `df` and `tablename` =>
print(formals(dfPutTable))    # prints 'df' and 'tablename'

# Load `df2export` to `SAMPLE_SCHEMA@SAMPLE_DB` =>
exit_status <- dfPutTable(df=df2export, tablename="DATA_FROM_R")

if (!exit_status==0) {
    stop("An error occurred attempting to create `DATA_FROM_R`. Exiting.")
}


dbDisconnect(conn)
dbUnloadDriver(oradrv)

A few things to note about dfPutTableAuth:

  • If a table with the same name as tablename exists in the target schema, it will first be purged prior to loading . This behavior can be modified by placing a third inner function argument named something like ‘delete_if_exists’, set to FALSE by default, allowing for finer-grained control over how to handle name collisions in a particular schema.

  • dbWriteTable returns TRUE upon successful table load, FALSE otherwise. If you anticipate a boolean return value from your implementation, there’s no need to coerce the returned value to 0 or 1.

In summary, we’ve demonstrated how to use R closures to wrap existing ROracle OraConnection methods for Oracle table access, permitting us to forego the provision of an database connection object at each function invocation, thus simplifying the call and response across the R-Oracle interface.