Hi,
I followed the instructions in Databases using R · R Views to read a large Oracle DB table using dplyr.
I use an RJDBC connection to read from the DB. Here is my R code:
devtools::install_github("tidyverse/dplyr")
devtools::install_github("tidyverse/dbplyr")
install.packages("DBI")
install.packages("RJDBC")
library(DBI)
library(RJDBC)
library(dplyr)
library(dbplyr)
jdbcDriver <- JDBC(driverClass="oracle.jdbc.OracleDriver", classPath="<path_to_ojdbc6.jar>")
jdbcConnection <- dbConnect(jdbcDriver, "", "", "")
sql_translate_env.JDBCConnection <- dbplyr:::sql_translate_env.Oracle
sql_select.JDBCConnection <- dbplyr:::sql_select.Oracle
sql_subquery.JDBCConnection <- dbplyr:::sql_subquery.Oracle
#This code works
sqlResult1 <- as.data.table(dbGetQuery(jdbcConnection, "<SQL_Query>")
#This code does not work
sqlResult2 <- tbl(jdbcConnection, sql("<SQL_Query>"))
Error in .verify.JDBC.result(r, "Unable to retrieve JDBC result set for ", :
Unable to retrieve JDBC result set for SELECT *
FROM (<SQL_Query>)
) "xkntoqrnaq"
FETCH FIRST 10 ROWS ONLY (ORA-00933: SQL command not properly ended
#This does not work either
tbl(jdbcConnection, in_schema('<SCHEMA_NAME>', '<TABLE_NAME>'))
Error in .verify.JDBC.result(r, "Unable to retrieve JDBC result set for ", :
Unable to retrieve JDBC result set for SELECT *
FROM (<SCHEMA_NAME>.<TABLE_NAME>)
FETCH FIRST 10 ROWS ONLY (ORA-00933: SQL command not properly ended
)
Even though I specifically use the Oracle-specific functions, the SQL query is appended with DB2-specific SQL like 'FETCH FIRST 10 ROWS ONLY'.
These are the versions of packages:
dplyr: ‘0.7.3.9000’
dbplyr: ‘1.1.0.9000’
RJDBC: '0.2.5'
DBI: '0.7'
Has anyone encountered a similar problem?