A suite of ROracle wrapper functions used to write R dataframes to Oracle, and read Oracle tables into R dataframes.

ROracleAPI.R

library(ROracle)

Sys.setenv(TZ='America/Chicago')
Sys.setenv(ORA_SDTZ='America/Chicago')

#=============================================
# Upload dataframe as Oracle database table  |          
#=============================================
dfToOracle <- function(dataframe, 
                       tablename, 
                       database, 
                       schema, 
                       password,
                       overwrite=TRUE, 
                       append=FALSE) {
    
    database     <- trimws(database)
    schema       <- trimws(schema)
    password     <- trimws(password)
    tablename    <- trimws(tablename)
    oracleDriver <- dbDriver("Oracle")
    conn         <- dbConnect(
                            oracleDriver, 
                            username=schema, 
                            password=password, 
                            dbname=database,
                            bulk_read=1000L, 
                            bulk_write=1000L, 
                            stmt_cache=0L,
                            external_credentials=FALSE, 
                            sysdba=FALSE)
    
    ### check whether `tablename` exists in `schema` =>
    if (dbExistsTable(conn, tablename)) {
        if (append==FALSE) {
            if (overwrite==TRUE) {
                dbRemoveTable(conn, tablename, purge=TRUE)
            } else {
                stop(paste("`",tablename, "` already exists in ", 
                           schema, "@", database, ".", sep=""))
            }
        }
    }
    
    ### write dataframe to tablename =>
    returnstatus=dbWriteTable(
                            conn=conn, 
                            name=tablename, 
                            value=dataframe, 
                            row.names=FALSE, 
                            ora.number=TRUE,
                            date=TRUE,
                            append=append)  
    
    if (returnstatus==TRUE) {
        print(paste("`", tablename, "` successfully loaded to ",
                    schema, "@", database, "...", sep=""),
                    quote=FALSE)
    } else {
        print(paste("An error was encountered attempting to load `", 
                    tablename, "` to ", schema, "@", database, ".", sep=""), 
              quote=FALSE)
        
    }
    dbDisconnect(conn)
    return(returnstatus)
}


#=====================================================
# Capture Oracle table data and return as data.frame |
#=====================================================
dfFromOracle <- function(database, 
                         schema, 
                         password,
                         SQLstr) {
    
    database     <- trimws(database)
    schema       <- trimws(schema)
    password     <- trimws(password)
    SQLstr       <- trimws(SQLstr)
    oracleDriver <- dbDriver("Oracle")
    conn         <- dbConnect(
                            oracleDriver, 
                            username=schema, 
                            password=password, 
                            dbname=database,
                            bulk_read=1000L, 
                            bulk_write=1000L, 
                            stmt_cache=0L,
                            external_credentials=FALSE, 
                            sysdba=FALSE)
    
    dataframe    <- dbGetQuery(conn, SQLstr)
    dbDisconnect(conn)
    return(dataframe)
}


#=====================================================================
# Provide `tablename` instead of SQLstr to capture Oracle table data |
#=====================================================================
dfCaptureTable <- function(db 
                           schema='CONNECTANALYSIS',
                           password='n518dytzb',
                           tablename) {
    
    database     <- trimws(db)
    schema       <- trimws(schema)
    password     <- trimws(password)
    tablename    <- trimws(tablename)
    oracleDriver <- dbDriver("Oracle")
    conn         <- dbConnect(
                            oracleDriver,
                            username=schema, 
                            password=password,
                            dbname=database,
                            bulk_read=1000L, 
                            bulk_write=1000L, 
                            stmt_cache=0L,
                            external_credentials=FALSE, 
                            sysdba=FALSE)
    
    if (!dbExistsTable(conn, tablename)) {
        dbDisconnect(conn)
        stop(paste("Table `", tablename, "` does not exist in ", 
                   schema, "@", database, ".", sep=""))
    }
    else {
        dataframe <- as.data.frame(dbReadTable(conn, 
                                               tablename, 
                                               schema=schema,
                                               row.names=NULL))
        dbDisconnect(conn)
    }
    return(dataframe)
}


#==========================================================
# Same as `dfCaptureTable', but retrieves multiple tables |
#==========================================================
dfCaptureTables <- function(db, 
                            schema,
                            password,
                            tablenames) {
    
    database     <- trimws(db)
    schema       <- trimws(schema)
    password     <- trimws(password)
    tablenames   <- unlist(tablenames)
    oracleDriver <- dbDriver("Oracle")
    results      <- list()
    conn         <- dbConnect(
                            oracleDriver,
                            username=schema, 
                            password=password,
                            dbname=database,
                            bulk_read=1000L, 
                            bulk_write=1000L, 
                            stmt_cache=0L,
                            external_credentials=FALSE, 
                            sysdba=FALSE)
    
    for (i in 1:length(tablenames)) {
        
        tbl <- trimws(tablenames[i])
        
        if (!dbExistsTable(conn, tbl)) {
            print(paste("Table `", tbl, "` does not exist in ", 
                        schema, "@", database, ".", sep=""), quote=FALSE)
            next
            
        } else {
            dataframe  <- dbReadTable(conn, 
                                      tbl, 
                                      schema,
                                      row.names=NULL))
            results[[i]] <- dataframe
        }
    }
    dbDisconnect(conn)
    return(results)
}


#==================================================================
# Closure version of dfFromOracle => Takes a connection object as |
# opposed to a separate db, schema and password arguments.        |
#                                                                 |
# First, pass `conn` to `dfCaptureTableClosure':                  |
#                                                                 |
#       dfCaptureTable <- dfCaptureTableClosure(conn)             |
#                                                                 |
# Next, pass SQLstr to `dfCaptureTable':                          |
#                                                                 |
#       df <- dfCaptureTable(SQLstr="SELECT * FROM TABLE")        |
#                                                                 |
#==================================================================
dfCaptureTableClosure <- function(connection, killConnection=FALSE) {
    conn <- connection
    function(SQLstr) {
        dataframe <- as.data.frame(dbGetQuery(conn, SQLstr))
        if (killConnection) dbDisconnect(conn)
    return(dataframe)
    }
}