RJDBC example using ojdb8.jar and dbplyr

rjdbc_dbplyr_example.R

# Connect Via RJDBC -------------------------------------------------------
library(RJDBC)
drv <- JDBC("oracle.jdbc.OracleDriver", classPath="~/import/instantclient_12_2/ojdbc8.jar")
con <- dbConnect(drv,"jdbc:oracle:thin:@server:1521:database", 
                 rstudioapi::askForSecret("Username"), 
                 rstudioapi::askForSecret("Database Password"))
dbGetQuery(con,"select count(*) from schema.table")


# Setup dplyr and dbplyr to work with RJDBC -------------------------------
# https://db.rstudio.com/databases/oracle/#known-issues
sql_translate_env.JDBCConnection <- dbplyr:::sql_translate_env.Oracle
sql_select.JDBCConnection <- dbplyr:::sql_select.Oracle
sql_subquery.JDBCConnection <- dbplyr:::sql_subquery.Oracle


# Use dplyr and dbplyr ----------------------------------------------------
library(dplyr)
library(dbplyr)

db_table <- tbl(con, in_schema("schema", "table"))

db_table %>% 
  filter(CURRENT_AGE >= 60) %>% 
  mutate(age = case_when(
    CURRENT_AGE  < 65 ~ "60 - 64",
    CURRENT_AGE  < 70 ~ "65 - 79",
    CURRENT_AGE  < 75 ~ "70 - 74",
    CURRENT_AGE  < 80 ~ "75 - 79",
    CURRENT_AGE  < 85 ~ "80 - 84",
    CURRENT_AGE >= 85 ~ "85+"
  )) %>% 
  count(age, GENDER) %>% 
  arrange(age, desc(n))