How to take a reference to a database table with dbplyr

General
1

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?

2

tbl is a dplyr function, not a dbplyr one. I get confused about which set belongs to which myself.

3

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

4

@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.

5

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
6

@edgararuiz, thanks for confirming that and the link.

1 Like
7

@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
8

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?

9

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.

10

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! :slight_smile:

1 Like
11

That's great! I'm glad it's working for you now