Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Handling of unsupported column types in Oracle DB, tbl() vs dbGetQuery() #1537

Open
michaelgrund opened this issue Aug 28, 2024 · 0 comments

Comments

@michaelgrund
Copy link

Maybe related to #369.

When requesting data from an Oracle DB via

library(tidyverse)
library(DBI)
library(ROracle)

drv <- dbDriver("Oracle")

con <- dbConnect(drv, 
                 dbname = "dbname",
                 username = "user",
                 password = "pw"
                 )

from some tables with unsupported column types tbl() does behave different to the approach when using dbGetQuery():

tbl(con, I("SYS.ALL_TAB_COLUMNS")) %>% 
  filter(OWNER == "AR") %>% 
  select(OWNER, TABLE_NAME, COLUMN_NAME)

gives the following error:

Error in `db_query_fields.DBIConnection()`:
! Can't query fields.
ℹ Using SQL: SELECT * FROM SYS.ALL_TAB_COLUMNS "q11" WHERE (0 = 1)
Caused by error in `.oci.GetQuery()`:
! unsupported column type
Backtrace:
  1. tbl(con, I("SYS.ALL_TAB_COLUMNS")) %>% select(OWNER)
  4. dplyr:::tbl.DBIConnection(con, I("SYS.ALL_TAB_COLUMNS"))
  6. dbplyr:::tbl.src_dbi(...)
  7. dbplyr::tbl_sql(c(subclass, "dbi"), src = src, from = from, ...)
 10. dbplyr:::dbplyr_query_fields(src$con, source)
 11. dbplyr:::dbplyr_fallback(con, "db_query_fields", ...)
 13. dbplyr:::db_query_fields.DBIConnection(con, ...)

while

query <- "
SELECT OWNER, TABLE_NAME, COLUMN_NAME
FROM SYS.ALL_TAB_COLUMNS
WHERE OWNER = 'AR'
"

dbGetQuery(con, query)

works as expected.

From the error message I understand that tbl() is doing a select on all columns (*) before slicing only the requested ones. Is this behavior wanted? Of course I don't know the unsupported columns initially and have to do trial and error to find them out but using tbl() seems to be no solution to me for this approach, right?

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Labels
None yet
Projects
None yet
Development

No branches or pull requests

1 participant