Oracle odbc connection not working

[crazybilly]

I have traditionally connected to our local data warehouse with some code like this:

con <- DBI::dbConnect(odbc::odbc(), "MY_ODBC_CON_NAME", uid = Sys.getenv('MY_USER_NAME'), pwd = Sys.getenv("MY_PASS"), timeout = 10)

When I do this in RStudio, everything works fine: I can use tbl() to create & query tables, connect/disconnect as needed, etc.

When I try this in Positron, though, things don't work as expected. The initial connection seems to execute without errors, but when I try to execute any DBI commands against con, I get an error like:

"Error in `UseMethod()`:
! no applicable method for 'tbl' applied to an object of class "Oracle"

Even DBI::dbDisconnect(con) gives me:

Error:
! unable to find an inherited method for function ‘dbDisconnect’ for signature ‘conn = "Oracle"’

There's a very real possibility, of course, that I'm doing something wrong, or something specific in our Oracle implementation (or my Windows-based odbc connnection), but I'd lean further that direction is the same code didn't work so well in RStudio. Maybe there's more I can do to help track it all down?

[DavisVaughan]

As a random guess, is it possible that in Positron you end up using a different version of R than what is being used in RStudio? Check with getRversion(). Could be that Positron is firing up an older version of R or something, with outdated packages. Not sure.

[crazybilly]

Good question--I should have included that info! The bad news is that I did check and confirm that I'm using R 4.4.0 in both places.

[DavisVaughan]

Does this work for you? If so can you please provide a more complete reprex of the code you are running?

library(DBI)
library(duckdb)
library(dbplyr)
library(dplyr)

con <- dbConnect(duckdb::duckdb())

dbWriteTable(con, "cars", mtcars)

tbl(con, "cars")

[crazybilly]

Yeah--that ran fine (after I installed the duckdb package)!

[juliasilge]

Great! Can you create a reprex that we can run that fails for you in Positron? Creating reprexes when dealing with databases is particularly difficult, but there are some suggestions/pointers in the dbplyr documentation. We don't need you to create a dbplyr reprex, obviously, but if you can give us code we can run, that will help us enormously in the quest to solve your problem.

[crazybilly]

Sure thing, though I'm puzzling through how to write a good reprex for this, since it seems to fall apart when connecting to our Oracle database! I suppose the best I can do is show the actual code I'm running--maybe ya'll can test against an Oracle db you have access to?

My connection is R -> ODBC -> Oracle. I tried connecting to a sample db via R -> ODBC -> SQLite and that worked fine, so it seems to be ODBC/Oracle-specific (though that hardly nails it down very tightly!)

I'm using:

• Windows 10
• R 4.4.0
• dbplyr 2.5.0
• DBI 1.2.3
• odbc 1.50

Here's the code I'm running:

library(dplyr)
library(dbplyr)
library(DBI)
library(odbc)

my_db <- DBI::dbConnect(odbc::odbc(), "MY_ODBC_CON_NAME", uid ='MY_USER_NAME', pwd = "MY_DB_PASSWORD", timeout = 10)

tbl(my_db, in_schema('DBW', 'AN_EXISTING_TABLE'))

When I run that in RStudio, I get the expected results: my_db is a valid connection to the database (and I can make tbls from it, etc). And the last line prints the first 10 rows of AN_EXISTING_TABLE.

In Positron, my_db seems to connect ok, but the last line returns the following error:

Error in `UseMethod()`:
! no applicable method for 'tbl' applied to an object of class "Oracle"

I tried several other known good (and known bad!) table names, with and without in_schema(), and get the same error every time.

Interestingly, I do NOT get an error if I just use DBI:

# returns a bunch of IDs as expected in Positron
DBI::dbGetQuery(my_db, "select ID from AN_EXISTING_TABLE")

Which leads to me believe the problem isn't actually in the database connection, but rather somewhere between tbl() and Oracle/ODBC (though how using a different IDE could affect that is beyond me!). If I look at the class of my_db, it looks the same between RStudio and Postiron:

class(my_db)

[1] "Oracle"
attr(,"package")
[1] "odbc"

I feel like I'm falling down on the "reproducible" side of reprex, but that's about all the details I can think to provide short of giving you access to our warehouse!

[DavisVaughan]

Can we see the output of dbGetInfo(my_db)? That will tell us the driver name to try and replicate on our side.

[crazybilly]

Sure thing:

$dbname
[1] ""

$dbms.name
[1] "Oracle"

$db.version
[1] "19.00.0000"

$username
[1] "myusername"

$host
[1] ""

$port
[1] ""

$sourcename
[1] "the_odbc_name"

$servername
[1] "the_tns_name"

$drivername
[1] "SQORA32.DLL"

$odbc.version
[1] "03.80.0000"

$driver.version
[1] "12.02.0010"

$odbcdriver.version
[1] "03.52"

$supports.transactions
[1] TRUE

$supports.catalogs
[1] FALSE

$supports.schema
[1] TRUE

$getdata.extensions.any_column
[1] TRUE

$getdata.extensions.any_order
[1] TRUE

[simonpcouch]

Thanks for the additional info, @crazybilly. Is that dbGetInfo() output from your RStudio connection or Positron connection? If from RStudio, could you also attempt dbGetInfo() from Positron and send the analogous output if it doesn't error out?

Oracle connections are sensitive to a few environmental variables, and I wonder if a change in envvars might be responsible for an unintended switch in driver or a dependency. (On MacOS, at least, I see a different Sys.getenv("PATH") in Positron and am unable to use my usual connection code to connect to Oracle.) What is Sys.getenv("PATH") in both RStudio and Positron? Same for Sys.getenv("TNS_ADMIN") and Sys.getenv("ORACLE_HOME").

Also, generally, the 12.02.0010 driver is quite dated. Is it possible for you to update your Instant Client to a more recent version?

[crazybilly]

That's the dbGetInfo() info from Positron (though I just checked and it matches what Rstudio returns as well). Sys.getenv("TNS_ADMIN") and Sys.getenv("ORACLE_HOME") return the same thing on both RStudio & Positron. PATH, though, is different:

var	is_same	rstudio	positron
TNS_ADMIN	TRUE	C:\Oracle\instantclient_12_2\network\admin	C:\Oracle\instantclient_12_2\network\admin
ORACLE_HOME	TRUE	C:/Oracle/instantclient_12_2_64	C:/Oracle/instantclient_12_2_64
OCI_LIB64	TRUE	C:/Oracle/instantclient_12_2_64	C:/Oracle/instantclient_12_2_64
OCI_LIB23	TRUE
OCI_INC	TRUE	C:/Oracle/instantclient_12_2_64/sdk/include	C:/Oracle/instantclient_12_2_64/sdk/include
PATH	FALSE	C:\ProgramData\Anaconda3\Scripts;C:\ProgramData\Anaconda3;C:\RBuildTools\4.4\x86_64-w64-mingw32.static.posix\bin;C:\RBuildTools\4.4\usr\bin;C:\Program Files\R\R-4.4.0\bin\x64;C:\Program Files (x86/\Common Files\Oracle\Java\javapath;C:\WINDOWS\system32;C:\WINDOWS;C:\WINDOWS\System32\Wbem;C:\WINDOWS\System32\WindowsPowerShell\v1.0;C:\WINDOWS\System32\OpenSSH;C:\Program Files\Google\Google Apps Sync;C:\Program Files\Google\Google Apps Migration;C:\Program Files\PuTTY;C:\rtools40;C:\Program Files\Common Files\ThinPrint;C:\Oracle\instantclient_12_2_64;C:\Oracle\instantclient_12_2_64\sdk\include;C:\Oracle\instantclient_12_2;C:\Oracle\instantclient_19_14;C:\Program Files\dotnet;C:\Program Files\Git\cmd;C:\Program Files\Positron\bin;C:\Users\MYACCOUNT\AppData\Local\Microsoft\WindowsApps;C:\Users\MYACCOUNT\AppData\Local\Programs\Microsoft VS Code\bin;C:\Users\MYACCOUNT.dotnet\tools;C:\Users\MYACCOUNT\mypath;C:\Program Files\RStudio\resources\app\bin\quarto\bin;C:\Program Files\RStudio\resources\app\bin\postback	C:\RBuildTools\4.4/x86_64-w64-mingw32.static.posix/bin;C:\RBuildTools\4.4/usr/bin;C:\Program Files (x86)\Common Files\Oracle\Java\javapath;C:\WINDOWS\system32;C:\WINDOWS;C:\WINDOWS\System32\Wbem;C:\WINDOWS\System32\WindowsPowerShell\v1.0;C:\WINDOWS\System32\OpenSSH;C:\Program Files\Google\Google Apps Sync;C:\Program Files\Google\Google Apps Migration;C:\Program Files\PuTTY;C:\rtools40;C:\Program Files\Common Files\ThinPrint;C:\Oracle\instantclient_12_2_64;C:\Oracle\instantclient_12_2_64\sdk\include;C:\Oracle\instantclient_12_2;C:\Oracle\instantclient_19_14;C:\Program Files\dotnet;C:\Program Files\Git\cmd;C:\Program Files\Positron\bin;C:\Users\MYACCOUNT\AppData\Local\Microsoft\WindowsApps;C:\Users\MYACCOUNT\AppData\Local\Programs\Microsoft VS Code\bin;C:\Users\MYACCOUNT.dotnet\tools;C:\Users\MYACCOUNT\mypath;

l experimented with upgrading my Oracle driver a little bit--I created a new odbc connection using the v19 driver (which I already had installed on my machine, apparently!). I get the same behavior as before when use that driver: RStudio seems fine, but Positron can't create a tbl() from a database table (but is able to run a query if I do DBI::dbGetQuery(my_db, "select * from A_KNOWN_TABLE").

I see that v23 is out nowadays! I'll see if I can download/install that and give a go soon.

[crazybilly]

A quick note: I installed Positron version 2024.08.0 build 31 this morning, and now everything is working fine!! The very things that triggered the problem before are returning data (rather than errors) as expected! Thanks for all your work on this!