Error connecting to Oracle Database via odbc in RStudio. Works fine from CLI R.

[mathman]

I am trying to establish a connection to an Oracle Database in RStudio (Version 2022.07.1 Build 554. Running on SUSE SLE15, SP4).

The connection should be established via an ldap-Server. Its address is in my ldap.ora file which is in /opt/instantclient_19/network/admin.

The code isql my_datasource does establish the connection.

Putting the following code in an R-file an executing via Rscript establishes the connection.

dbConnect(
  odbc::odbc(),
  dsn = "my_datasource",
  UID = "***",
  PWD = "***"
)

Trying to source the same script in RStudio (or RStudio Server) throws an error:

Error: nanodbc/nanodbc.cpp:1021: 00000: [Oracle][ODBC][Ora]ORA-12154: TNS:could not resolve the connect identifier specified

[brianstamper]

I've been dealing with a similar but different Oracle situation between RStudio and CLI R. Some of what I've been trying along the way might help.

Try this in both environments:

Sys.getenv(c('LD_LIBRARY_PATH', 'ORACLE_HOME', 'TNS_ADMIN'))

It may be the case you need to set some environment variables, perhaps in an ~/.Rprofile.

(My situation is rather different in that I am using ROracle instead of ODBC and that package won't load in RStudio. In my case the environment variables don't even seem to matter, but it might help in your situation?)

[mathman]

Thanks for your input Brian!

Based on your hint I checked the values of the three environment variables in CLI R (connection is working from there) and in rstudio (connection not working):

• TNS_ADMIN is empty in every case
• LD_LIBRARY_PATH is identical
• ORACLE_HOME points to /opt/instantclient_19 in the working environment. It is empty in rstudio.

I thought I had found the problem and tried the following:

• Sys.setenv(ORACLE_HOME = "/opt/instantclient_19")
• Create a wrapper script for rstudio with export ORACLE_HOME = /opt/instantclient_19

Both attempts (and a lot of similiar messing around with environment variables) don't change anything. I still get the same error message!