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
-
install unixodbc using homebrew
-
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
- Move the files
sudo mkdir /usr/local/oracle
sudo mv ~/Downloads/instantclient /usr/local/oracle/instantclient
- 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
- 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
- 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
- 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 =
- 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
- 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