How do you access tables not in the default Oracle schema using dplyr?

Question

With the release of dplyr 0.7.0, it is now supposedly easy to connect to Oracle using the odbc package. However, I am running into a problem accessing tables not inside the default schema (for me it is my username). For example, suppose there is the table TEST_TABLE in schema TEST_SCHEMA. Then, example SQL syntax to get data would be: select * from TEST_SCHEMA.TEST_TABLE'.

To do the same in `dplyr, I am trying the following:

# make database connection using odbc: [here's a guide][1]
oracle_con <- DBI::dbConnect(odbc::odbc(), "DB")
# attempt to get table data
tbl(oracle_con, 'TEST_SCHEMA.TEST_TABLE')

Now, this leads to an error message:

Error: <SQL> 'SELECT *
FROM ("TEST_SCHEMA.TEST_TABLE") "zzz12"
WHERE (0 = 1)'
  nanodbc/nanodbc.cpp:1587: 42S02: [Oracle][ODBC][Ora]ORA-00942: table or view does not exist

I think the problem here is the double quotation marks, as:

DBI::dbGetQuery(oracle_con, "select * from (TEST_SCHEMA.TEST_TABLE) where rownum < 100;")

works fine.

Answer 1

I struggled with this for a while until I found the solution at the bottom of the introduction to dbplyr. The correct syntax to specify the schema and table combo is:

tbl(oracle_con, in_schema('TEST_SCHEMA', 'TEST_TABLE'))

As an aside, I think the issue with quotation marks is lodged here: https://github.com/tidyverse/dplyr/issues/3080

There are also the following alternate work-arounds that may be suitable depending on what you wish to do. Since the connection used DBI, one can alter the schema via:

DBI::dbSendQuery(oracle_con, "alter session set current_schema = TEST_SCHEMA")

after which tbl(oracle_con, 'TEST_TABLE') will work.

Or, if you have create view privileges, you can create a "shortcut" in your default schema to any table you are interested in:

DBI::dbSendQuery(oracle_con, "CREATE VIEW TEST_TABLE AS SELECT *
                 FROM TEST_SCHEMA.TEST_TABLE")

Note that the latter may be more suitable for applications where you wish to copy local data to the database for a join, but do not have write access to the table's original schema.