Error connecting to Oracle Database via odbc in RStudio. Works smoothly when starting from Terminal

mathman 0
Viewed 17

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 by 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
odbc unixodbc rstudio oracle r
1 Answers

There should be a .Renviron file in your home directory. If not, you'll want to create a text file and save it with .Renviron as the filename. In that file, you'll want to define a path for the ODBCINI environmental variable to an ".odbc.ini" or "odbc.ini" file. For example,

ODBCINI = ~/.odbc.ini

The file in this path should list all DSNs you can use to make a connection. For more information, please see https://solutions.rstudio.com/db/best-practices/drivers/.

Ted M.0
Related