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

with release of dplyr 0.7.0, supposedly easy connect oracle using odbc package. however, running problem accessing tables not inside default schema (for me username). example, suppose there table test_table in schema test_schema. then, example sql syntax data be: select * test_schema.test_table'.

to same in `dplyr, trying following:

# make database connection using odbc: [here's guide][1] oracle_con <- dbi::dbconnect(odbc::odbc(), "db") # attempt table data tbl(oracle_con, 'test_schema.test_table') 

now, leads error message:

error: <sql> 'select * ("test_schema.test_table") "zzz12" (0 = 1)'   nanodbc/nanodbc.cpp:1587: 42s02: [oracle][odbc][ora]ora-00942: table or view not exist 

i think problem here double quotation marks, as:

dbi::dbgetquery(oracle_con, "select * (test_schema.test_table) rownum < 100;") 

works fine.

i struggled while until found solution @ bottom of introduction dbplyr. correct syntax specify schema , table combo is:

tbl(oracle_con, in_schema('test_schema', 'test_table')) 

as aside, think issue quotation marks lodged here: https://github.com/tidyverse/dplyr/issues/3080

there following alternate work-arounds may suitable depending on wish do. since connection used dbi, 1 can alter schema via:

dbi::dbsendquery(oracle_con, "alter session set current_schema = test_schema") 

after tbl(oracle_con, 'test_table') work.

or, if have create view privileges, can create "shortcut" in default schema table interested in:

dbi::dbsendquery(oracle_con, "create view test_table select *                  test_schema.test_table") 

note latter may more suitable applications wish copy local data database join, not have write access table's original schema.