Error in dplyr::tbl() function while trying to read an Oracle DB table


#1

Hi,

I followed the instructions in https://rviews.rstudio.com/2017/05/17/databases-using-r/ 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?


#2

Hi, yes, this is more likely due to the version of the Oracle database you are connecting to. The current translation in dbplyr was based on 12c syntax, which support FETCH FIRST, which is not supported on older versions. I have a PR that fixes that problem: https://github.com/tidyverse/dbplyr/pull/27 . The best approach at this time is to use the PR’s version until is merged to the master. Thanks.


#3

Thanks! You are right, I am trying to connect to Oracle 11g DB.