Connecting to Oracle DB with odbc library fails on macOS 10.15.5

nickodemus · July 20, 2020, 8:47pm

Hi,

I am trying to connect to an Oracle database with the odbc and DBI packages on a Mac. The error I am receiving is

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

I can successfully query the database from isql, but cannot from R (command line) or RStudio. I have found similar problems on RStudio's github page, and here and here on RStudio Community, but have not been able to resolve the issue with their solutions.

I have recreated the error on a vm using the following steps

Install unixodbc from homebrew
Download instant client files 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

Combine all the instant client files into one and move that file.

cp -a instantclient_12_2-2/. instantclient
cp -a instantclient_12_2-3/. instantclient
cp -a instantclient_12_2-4/. instantclient
cp -a instantclient_12_2-5/. instantclient

sudo mkdir /usr/local/oracle  
sudo mv ~/Downloads/instantclient /usr/local/oracle/instantclient

Remove all 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 odbc.ini

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

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         =

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=****

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

I run the following connection 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"

Other info:

> 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

alexkgold · July 29, 2020, 12:57pm

Hi @nickodemus,

It looks to me like odbcinst -j is returning the correct output, and you are able to successfully run isql, so I think you're almost there. For some reason, your R code isn't actually making use of the DSN you're configuring, and therefore R is looking in the wrong place for the driver.

Can you share the isql command that's running successfully? That might be a hint here.

It looks like you've got a few different pieces configured here, but the simplest pattern is usually to:

Define the DSN in odbc.ini with a name. In this case, it looks like you've defined a DSN named banner in the odbc.ini.
Use that DSN in your DBI code. Since your DSN is named banner, your DBI code would look something like
con <- DBI::dbConnect(odbc::odbc(), "banner", <additional arguments not in the odbc.ini>),
The odbc.ini will either directly specify the driver path, or use a name defined in odbcinst.ini to look up the driver path. It looks like your odbc.ini is directly specifying the driver, so you shouldn't need the odbcinst.ini at all.

There's a little more of a step-by-step walkthrough here: https://db.rstudio.com/best-practices/drivers/

It looks to me like steps 1-6 above (thanks for the super-thorough walkthrough, btw) are about getting the Oracle driver to work properly, but now it's just about making sure the R install knows how to find it.

What happens if you try connecting using the DSN name?

nickodemus · July 29, 2020, 3:42pm

Hi @alexkgold,

The isql command is

isql -v "banner" [username] [password]
+---------------------------------------+
| Connected!                            |
|                                       |
| sql-statement                         |
| help [tablename]                      |
| quit                                  |
|                                       |
+---------------------------------------+

SQL>

When I run using the DBI code

DBI::dbConnect(odbc::odbc(),
                "banner",
                UID    = ****,
                PWD    = ****)

It returns the same error.

yateam · August 4, 2020, 10:10pm

Hi @nickodemus. Thanks for looping me in. I just got my connection working! I can't pinpoint exactly what your error is, but one thing I noticed in your original post was the set of commands you used to modify the library:

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

The middle two commands don't include "instantclient" in the path. I'm guessing those files are actually located at /usr/local/oracle/instantclient/...

In your instantclient directory, use the command

otool -l libsqora.dylib.12.1

to see the full definition of the library. I checked each path in the definition and 2 of the files didn't exist at the place I specified. So I made the following changes:

install_name_tool -change "/usr/local/oracle/libclntsh.dylib.12.1" "/usr/local/oracle/instantclient/libclntsh.dylib.12.1" libsqora.dylib.12.1
install_name_tool -change "/usr/local/oracle/libodbcinst.2.dylib" "/usr/local/oracle/instantclient/libodbcinst.2.dylib" libsqora.dylib.12.1

Also, for me the file "libodbcinst.2.dylib" wasn't originally in the instantclient directory - it wasn't part of any of the packages I unzipped from Oracle. I found it in my homebrew installation of unixodbc at /usr/local/Cellar/unixodbc/2.3.7/lib

I ended up copying it and the other files in that directory into /usr/local/oracle/instantclient/ so everything would be in one place. I don't know if that's strictly necessary - you might be able to point to it where it is. But now that it's working, I'm not changing anything! I hope this helps.

nickodemus · August 5, 2020, 3:24am

Hi @yateam. That worked!

I tried just copying "libodbcinst.2.dylib" into instantclient and that did not work for me. Once I copied the whole file like you suggested it did.

Thank you so much for your help on this!

system · August 12, 2020, 3:24am

This topic was automatically closed 7 days after the last reply. New replies are no longer allowed.