Connecting to Oracle DB with odbc library fails on macOS Catalina

I am attempting to connect to an Oracle database using the odbc package on macOS Catalina. I have installed Oracle Instant Client, including the ODBC package, and can connect with isql. When I attempt to connect with RStudio or R in the command line, I get the following error.

Error: nanodbc/nanodbc.cpp:983: 00000: [unixODBC][Driver Manager]Can't open lib '/usr/local/lib/libsqora.dylib.12.1' : file not found 

I found similar issues on RStudio's github page, and here and here in RStudio Community, but so far those solutions have not worked for me.

I have reproduced the issue on a vm using the following steps

  1. install unixodbc using homebrew

  2. Download instant client from Oracle

instantclient-basic-macos.x64-12.2.0.1.0-2.zip
instantclient-sqlplus-macos.x64-12.2.0.1.0-2.zip
instantclient-sdk-macos.x64-12.2.0.1.0-2.zip
instantclient-jdbc-macos.x64-12.2.0.1.0-2.zip
instantclient-odbc-macos.x64-12.2.0.1.0-2.zip
  1. Move the files
sudo mkdir /usr/local/oracle  
sudo mv ~/Downloads/instantclient /usr/local/oracle/instantclient 
  1. Remove the apple quarantine tags
# go to the instant client directory
cd /usr/local/oracle/instantclient
# change all the permissions so that you can alter the file attibutes
chmod u+w *
# change to the parent directory and remove all quarantine attributes 
cd ..
xattr -r -d -s com.apple.quarantine instantclient
  1. Set up .bash_profile
export ORACLE_HOME=/usr/local/oracle/instantclient  
export OCI_HOME=$ORACLE_HOME  
export OCI_LIB_DIR=$OCI_HOME  
export OCI_INCLUDE_DIR=$OCI_HOME/sdk/include  
export NLS_LANG=AMERICAN_AMERICA.UTF8  
export DYLD_LIBRARY_PATH=$OCI_LIB_DIR
export PATH=$ORACLE_HOME:$PATH
  1. Fix the bug in /usr/local/oracle/odbc_update_ini.sh
    by changing line 120 from
SO_NAME=libsqora.so.12.1

to

SO_NAME=libsqora.dylib.12.1
  1. Set up the odbcinst.ini and odbc.ini files
cd /usr/local/oracle/instantclient
sudo odbc_update_ini.sh /usr/local
sudo chown $USER ~/.odbc.ini

running odbcinst -j returns

unixODBC 2.3.7
DRIVERS............: /usr/local/etc/odbcinst.ini
SYSTEM DATA SOURCES: /usr/local/etc/odbc.ini
FILE DATA SOURCES..: /usr/local/etc/ODBCDataSources
USER DATA SOURCES..: /Users/matt/.odbc.ini
SQLULEN Size.......: 8
SQLLEN Size........: 8
SQLSETPOSIROW Size.: 8

odbc.ini

[ODBC Data Sources]
banner = [Oracle ODBC Driver]

[banner]
AggregateSQLType=FLOAT
Application Attributes=T
Attributes=W
BatchAutocommitMode=IfAllSuccessful
BindAsFLOAT=F
CacheBufferSize=20
CloseCursor=F
DisableDPM=F
DisableMTS=T
DisableRULEHint=T
Driver=/usr/local/oracle/instantclient/libsqora.dylib.12.1
DSN=banner
EXECSchemaOpt=
EXECSyntax=T
Failover=T
FailoverDelay=10
FailoverRetryCount=10
FetchBufferSize=64000
ForceWCHAR=F
LobPrefetchSize=8192
Lobs=T
Longs=T
MaxLargeData=0
MaxTokenSize=8192
MetadataIdDefault=F
QueryTimeout=T
ResultSets=T
ServerName=****
SQLGetData extensions=F
SQLTranslateErrors=F
StatementCache=F
Translation DLL=
Translation Option=0
UseOCIDescribeAny=F
UserID=****

odbcinst.ini

[Oracle 12c ODBC driver]
Description     = Oracle ODBC driver for Oracle 12c
Driver          = /usr/local/oracle/instantclient/libsqora.dylib.12.1
Setup           =
FileUsage       =
CPTimeout       =
CPReuse         = 
  1. Following this post, change relative paths to absolute paths
install_name_tool -id "/usr/local/oracle/instantclient/libsqora.dylib.12.1" libsqora.dylib.12.1
install_name_tool -change "@rpath/libclntsh.dylib.12.1" "/usr/local/oracle/libclntsh.dylib.12.1" libsqora.dylib.12.1
install_name_tool -change "@rpath/libodbcinst.2.dylib" "/usr/local/oracle/libodbcinst.2.dylib" libsqora.dylib.12.1
install_name_tool -rpath "@executable_path/../../oracle/lib" "/usr/local/lib" libsqora.dylib.12.1
  1. Create symlinks to the following files
sudo ln -s /usr/local/oracle/instantclient/*.dylib.12.1 /usr/local/lib 
sudo ln -s /usr/local/oracle/instantclient/*.dylib /usr/local/lib

# link to R libraries
sudo ln -s /usr/local/oracle/instantclient/*.dylib.12.1 /Library/Frameworks/R.framework/Resources/lib
sudo ln -s /usr/local/oracle/instantclient/*.dylib /Library/Frameworks/R.framework/Resources/lib

I then run the following script to produce the error

library(DBI)
library(odbc)
drv <- 'Oracle 12c ODBC driver'
host <- ****
port <- ****
sid <- ****
con <- DBI::dbConnect(odbc::odbc(),
                      Driver = drv,
                      Host   = host,
                      SVC    = sid,
                      UID    = ****,
                      PWD    = ****,
                      Port   = port)

System Details

RStudio Edition : Desktop
RStudio Version : 1.3.1056
OS Version      : MacOS Catalina
R Version       : 4.0.2 (2020-06-22) -- "Taking Off Again"
> Sys.info()
sysname 
"Darwin" 
release 
"19.6.0" 
version 
"Darwin Kernel Version 19.6.0: Sun Jul  5 00:43:10 PDT 2020; root:xnu-6153.141.1~9/RELEASE_X86_64" 
nodename 
"matts-Mac.local" 
machine 
"x86_64" 
login 
"root" 
user 
"matt" 
effective_user 
"matt" 
> sessionInfo()
R version 4.0.2 (2020-06-22)
Platform: x86_64-apple-darwin17.0 (64-bit)
Running under: macOS Catalina 10.15.6

Matrix products: default
BLAS:   /System/Library/Frameworks/Accelerate.framework/Versions/A/Frameworks/vecLib.framework/Versions/A/libBLAS.dylib
LAPACK: /Library/Frameworks/R.framework/Versions/4.0/Resources/lib/libRlapack.dylib

locale:
[1] en_US.UTF-8/en_US.UTF-8/en_US.UTF-8/C/en_US.UTF-8/en_US.UTF-8

attached base packages:
[1] stats     graphics  grDevices utils     datasets  methods   base     

other attached packages:
[1] odbc_1.2.3 DBI_1.1.0 

loaded via a namespace (and not attached):
 [1] bit_1.1-15.2    compiler_4.0.2  hms_0.5.3       tools_4.0.2     Rcpp_1.0.5      bit64_0.9-7.1   vctrs_0.3.2     blob_1.2.1     
 [9] pkgconfig_2.0.3 rlang_0.4.7   

I had a similar issue, it could be related to a security protocol of macOS Catalina. If this is the case, you should see a message when attempting to connect to Oracle db on the Security & Privacy window (System Preference -> Security & Privacy) a security message, and you should enable it. You may need to repeat this few times until no more messages prompt

Thanks for the quick response. I thought that too after I read your post on the issue, but I think removing the quarantine tags in step 4 addresses that. I have not seen the security message in the Security & Privacy window.

I am encountering this issue after upgrading from High Sierra to Mojave. I've tried the same things to repair the connection and also tried on a second computer running Catalina with the same results. @nickodemus has great steps for reproducing. Are there other suggestions?

Hi @yateam. I have gotten a response from @alexkgold in this thread.

Sorry that there are two threads. I switched the category on my thread and it opened a new one.