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

Include synonyms in odbcListObjects(), dbListTables(), and the Connections Pane #779

Open
ThomasSoeiro opened this issue Apr 2, 2024 · 1 comment

Comments

@ThomasSoeiro
Copy link

ThomasSoeiro commented Apr 2, 2024

The issue discussed in #773 for SQL Server also applies to Oracle. So here is a separate issue as suggested by @simonpcouch.

In Oracle, synonyms are available in {ALL|USER}_SYNONYMS.

Same reprex as in #773 (comment):

library(DBI)
con <- dbConnect(odbc::odbc(), dsn = "zzz")

dbExecute(con, "create table test (x int)")
# [1] 0

user <- dbGetQuery(con, "select user from dual")$USER

# confirm that we can find the table:
odbc::odbcListObjects(con, schema = user)
#   name  type
# 1 TEST table
dbListTables(con)
# [1] "TEST"

# make a synonym and show that it can't be found:
dbExecute(con, "create synonym test2 for test")
# [1] 0
odbc::odbcListObjects(con, schema = user)
#   name  type
# 1 TEST table
dbListTables(con)
# [1] "TEST"

However:

Database
dbGetInfo(con)
# $dbname
# [1] ""
# 
# $dbms.name
# [1] "Oracle"
# 
# $db.version
# [1] "19.00.0000"
# 
# $username
# [1] ""
# 
# $host
# [1] ""
# 
# $port
# [1] ""
# 
# $sourcename
# [1] "zzz"
# 
# $servername
# [1] "zzz"
# 
# $drivername
# [1] "RStudio Oracle ODBC Driver"
# 
# $odbc.version
# [1] "03.52"
# 
# $driver.version
# [1] "2.0.2.1002"
# 
# $odbcdriver.version
# [1] "03.80"
# 
# $supports.transactions
# [1] TRUE
# 
# $getdata.extensions.any_column
# [1] TRUE
# 
# $getdata.extensions.any_order
# [1] TRUE
# 
# attr(,"class")
# [1] "Oracle"      "driver_info" "list"       
Session Info
sessionInfo()
# R version 4.1.2 (2021-11-01)
# Platform: x86_64-pc-linux-gnu (64-bit)
# Running under: Red Hat Enterprise Linux Server 7.8 (Maipo)
# 
# Matrix products: default
# BLAS/LAPACK: /usr/lib64/libopenblasp-r0.3.3.so
# 
# locale:
#  [1] LC_CTYPE=fr_FR.UTF-8       LC_NUMERIC=C               LC_TIME=fr_FR.UTF-8        LC_COLLATE=fr_FR.UTF-8    
#  [5] LC_MONETARY=fr_FR.UTF-8    LC_MESSAGES=fr_FR.UTF-8    LC_PAPER=fr_FR.UTF-8       LC_NAME=C                 
#  [9] LC_ADDRESS=C               LC_TELEPHONE=C             LC_MEASUREMENT=fr_FR.UTF-8 LC_IDENTIFICATION=C       
# 
# attached base packages:
# [1] stats     graphics  grDevices datasets  utils     methods   base     
# 
# other attached packages:
# [1] DBI_1.0.0
# 
# loaded via a namespace (and not attached):
#  [1] bit_1.1-14      odbc_1.3.5      compiler_4.1.2  ellipsis_0.3.2  hms_1.1.1       tools_4.1.2     Rcpp_1.0.7      bit64_0.9-7    
#  [9] vctrs_0.3.8     blob_1.2.2      lifecycle_1.0.1 pkgconfig_2.0.2 rlang_0.4.12   
@simonpcouch
Copy link
Collaborator

Thanks for filing here!

Yup, I hear you that there are performance concerns here. May be worth making the inclusion of synonyms opt-in.

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

2 participants