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)
    }
}