This is the error I am getting from RStudio (and R, run using sudo):
> odbc::dbConnect(odbc::odbc(), driver = "/opt/oracle/instantclient_23_4/libsqora.so.23.1")
Error: nanodbc/nanodbc.cpp:1138: 00000
[unixODBC][Driver Manager]Can't open lib '/opt/oracle/instantclient_23_4/libsqora.so.23.1' : file not found
Running isql with the DSN leads to:
[28000][unixODBC][Oracle][ODBC][Ora]ORA-01017: invalid username/password; logon denied
So maybe the problem lies in the R odbc package?
Session info:
R version 4.4.0 (2024-04-24)
Platform: x86_64-pc-linux-gnu
Running under: Ubuntu 22.04.4 LTS
other attached packages:
[1] odbc_1.4.2 DBI_1.2.2
libsqora is set with permissions 777:
ls -l /opt/oracle/instantclient_23_4/libsqora.so.23.1 -rwxrwxrwx 1 rdpdp rdpdp 802848 Apr 25 01:38 /opt/oracle/instantclient_23_4/libsqora.so.23.1
and:
odbcinst --version
unixODBC 2.3.9
--- EDIT ---
Thanks to comments here, I have used system to produce some more diagnostics. After running R from terminal, I do system("isql -v test asdf asdf"), which returns
[01000][unixODBC][Driver Manager]Can't open lib '/opt/oracle/instantclient_23_4/libsqora.so.23.1' : file not found
[ISQL]ERROR: Could not SQLConnect
This is because:
system('ldd /opt/oracle/instantclient_23_4/libsqora.so.23.1')
linux-vdso.so.1 (0x00007fff1a9bc000)
/opt/datadog/apm/inject/launcher.preload.so (0x0000798d847fb000)
libdl.so.2 => /usr/lib/x86_64-linux-gnu/libdl.so.2 (0x0000798d847f6000)
libm.so.6 => /usr/lib/x86_64-linux-gnu/libm.so.6 (0x0000798d8470f000)
libpthread.so.0 => /usr/lib/x86_64-linux-gnu/libpthread.so.0 (0x0000798d8470a000)
librt.so.1 => /usr/lib/x86_64-linux-gnu/librt.so.1 (0x0000798d84705000)
libaio.so.1 => /usr/lib/x86_64-linux-gnu/libaio.so.1 (0x0000798d846fe000)
libresolv.so.2 => /usr/lib/x86_64-linux-gnu/libresolv.so.2 (0x0000798d846ea000)
libclntsh.so.23.1 => not found
libclntshcore.so.23.1 => not found
libodbcinst.so.2 => /usr/lib/x86_64-linux-gnu/libodbcinst.so.2 (0x0000798d846c1000)
libc.so.6 => /usr/lib/x86_64-linux-gnu/libc.so.6 (0x0000798d84400000)
/lib64/ld-linux-x86-64.so.2 (0x0000798d848be000)
libltdl.so.7 => /usr/lib/x86_64-linux-gnu/libltdl.so.7 (0x0000798d846b4000)
i.e. two files are not linked, because:
system("echo $LD_LIBRARY_PATH")
/usr/lib/R/lib:/usr/lib/x86_64-linux-gnu:/usr/lib/jvm/default-java/lib/server
This can be fixed with running Sys.setenv(LD_LIBRARY_PATH="/opt/oracle/instantclient_23_4"), after which the command system("isql -v test asdf asdf") returns:
[28000][unixODBC][Oracle][ODBC][Ora]ORA-01017: invalid username/password; logon denied
Help: https://docs.oracle.com/error-help/db/ora-01017/
[ISQL]ERROR: Could not SQLConnect
This means that now it is fixed in the shell R runs. Unfortunately odbc still cannot connect. So I suspect it has something to do with what odbc thinks is the LD_LIBRARY_PATH.
--- EDIT 20th May 2024 ---
Interestingly for system("isql -v ...") the oracle client installation folder needs to be first on the path. i.e.:
Sys.setenv(LD_LIBRARY_PATH=paste0("/opt/oracle/instantclient_23_4", ':', '/lib64/', ':', Sys.getenv('LD_LIBRARY_PATH')))
works, but:
Sys.setenv(LD_LIBRARY_PATH=paste0(Sys.getenv('LD_LIBRARY_PATH'), ':', "/opt/oracle/instantclient_23_4", ':', '/lib64/'))
doesn't.
I have also tried reinstalling odbc using:
withr::with_makevars(c(PKG_LIBS="-L/opt/oracle/instantclient_23_4/ -L/lib64/"), install.packages("odbc"), assignment="+=")
with no success.
Trying dyn.load on the problematic linked libraries leads to a different error:
dyn.load("/opt/oracle/instantclient_23_4/libclntsh.so.23.1")
dyn.load("/opt/oracle/instantclient_23_4/libclntshcore.so.23.1")
dyn.load("/opt/oracle/instantclient_23_4/libnnz.so")
> DBI::dbConnect(odbc::odbc(), 'test', pwd = 'asdf')
Error: nanodbc/nanodbc.cpp:1138: 00000
[unixODBC][Driver Manager]Driver's SQLAllocHandle on SQL_HANDLE_HENV failed
I struggled for a few days to install Oracle instant client into a Docker container and connect with R. I don't think any of my struggles were with R specifically, but I don't remember all the details. I can share the Dockerfile here in case it's useful.
Maybe the answer is somewhere in these Dockerfile commands.
Two other ideas
Are you installing the .ini file with the odbc_update_ini.sh script that comes with the Oracle instant client install? This script is kind of tricky and you have to give it some specific arguments. See the command under the comment # writes odbc ini file for an example.
odbc::odbcListDrivers() is a useful function to run when debugging odbc connections.
FROM rocker/shiny-verse
# system
RUN apt-get update
RUN apt-get install -y unixodbc alien libaio1
# oracle driver installations:
# https://www.oracle.com/database/technologies/instant-client/linux-x86-64-downloads.html#ic_x64_inst
# https://www.oracle.com/database/technologies/releasenote-odbc-ic.html
# paths
RUN sh -c 'echo /usr/lib/oracle/21/client64/lib/ > /etc/ld.so.conf.d/oracle.conf'
RUN ldconfig
# install instant client basic lite
# this is a prerequisite for the odbc driver
# to install an rpm file on Ubuntu, need alien
RUN wget https://download.oracle.com/otn_software/linux/instantclient/2110000/oracle-instantclient-basiclite-21.10.0.0.0-1.el8.x86_64.rpm
RUN alien -i oracle-instantclient-basiclite-21.10.0.0.0-1.el8.x86_64.rpm
# install instant client odbc
RUN wget https://download.oracle.com/otn_software/linux/instantclient/2110000/oracle-instantclient-odbc-21.10.0.0.0-1.el8.x86_64.rpm
RUN alien -i oracle-instantclient-odbc-21.10.0.0.0-1.el8.x86_64.rpm
RUN export PATH=/usr/lib/oracle/21/client64/bin:$PATH
# writes odbc ini file
RUN /usr/lib/oracle/21/client64/bin/odbc_update_ini.sh "/" "/usr/lib/oracle/21/client64/lib" "oracle"
# test in R
RUN R -e 'install.packages("odbc")'
CMD R -e '\
library(odbc); \
library(DBI); \
con <- dbConnect(odbc(), Driver = "oracle", DBQ = "xxx&yy.com:1521/appsdv", \
UID = "myid", PWD = "mypd", Port = 1521); \
con |> dbListTables(schema_name = "MYSCHEMA") |> print()'