I am trying to use dbplyr
(version 1.1.0) with an Oracle database. On the official page and on other online resources (as GitHub - tidyverse/dbplyr: Database (DBI) backend for dplyr for instance) they suggest to use the tbl
function but this function seems not to be available anymore.
Hence how can I take the reference in order to query the table with the dplyr
-like verbs?
tbl
is a dplyr
function, not a dbplyr
one. I get confused about which set belongs to which myself.
Just a quick call-out on using dplyr
with an Oracle database. Most times the default schema is not going to be used, which means that you need to specify the schema in the tbl()
command. This is done via the in_schema()
command, and it's used like this: db_table <- tbl(con, in_schema(my_schema, my_table)
)
@edgararuiz, is there any difference in the order in which dbplyr
and dplyr
are loaded? ident
and sql
are masked from the first one to be loaded, but I don't know whether this matters.
Hi @martin.R , it doesn't because those functions in dplyr
are simple wrappers to the same sql()
and ident()
functions in dbplyr
. The explanation of why, as well as the actual functions for sql()
and ident()
, are in this dplyr
script: https://github.com/tidyverse/dplyr/blob/16fa8a84220b97a40c711645bf225b3dd521bc16/R/compat-dbplyr.R
1 Like
@edgararuiz, thanks for confirming that and the link.
1 Like
@edgararuiz Even if I try to do what you say I get this error when I use the tbl
function:
tbl(conn, in_schema("my_schema", "my_table"))
Error in .oci.SendQuery(conn, statement, data = data, prefetch = prefetch, :
ORA-00933: SQL command not properly ended
Ok, I think it may be one of two things:
1 - Are you using an ODBC driver along the odbc
package to connect? If not, how are you connecting to the DB?
2 - Is the Oracle DB you are connecting to version 11 or below?
The version is 12.1.0.2.0 and I'm using the ROracle
package. With the dbReadTable
function I can get the data from the table.
In the end I did what @edgararuiz suggested in https://github.com/tidyverse/dplyr/issues/2928
sql_translate_env.OraConnection <- dbplyr:::sql_translate_env.Oracle
sql_select.OraConnection <- dbplyr:::sql_select.Oracle
sql_subquery.OraConnection <- dbplyr:::sql_subquery.Oracle
and it works!
1 Like
That's great! I'm glad it's working for you now