Oracle 12.2 ODBC driver installation on MacOS with unixodbc

dseverski

I've had a devil of a time figuring out how to get the 12.2 Oracle Instant Client (Basic Lite) and ODBC drivers working under MacOS Sierra. Repeating some content from https://stackoverflow.com/questions/48635297/how-to-get-macos-oracle-odbc-client-working-with-unixodbc , I've extracted the Basic Lite client and ODBC file to /opt/ora12/ and setup the symlinks to /usr/local/lib, but when I point a DSN to the libsqora library, the connections always fail. Looking at the lib with `otool -L` it looks like some of the dependencies are not being followed, specifically all those linked with `@rpath`. If I pull down the 12.2 SQLPlus client, extract that into /opt/ora12, that is able to find all the libraries fine, but I don't know how to get unixODBC to do the same when it's @rpath doesn't know about my /opt/ora12 location.

Any pointers/docs on where I'm going wrong would be super appreciated. I'm not very familiar with DYLIB loading under MacOS so I'm likely misunderstanding something basic here.

David

Sdhamoth-Oracle

I'm able to install unixODBC & Instant Client ODBC driver on Mac OS and run ODBC applications.

I'm sharing the step by step procedure I followed to install unixODBC and Instant Client ODBC driver on Mac OS:

Installing unixODBC DM:

1) Download unixODBC-2.3.1.tar.gz  from ftp://ftp.unixodbc.org/pub/unixODBC/
2) tar -zxvf unixODBC-2.3.1.tar.gz
3) cd unixODBC-2.3.1
4) ./configure
5) ./make
6) ./make install

Installing Instant Client 12.2:

1. Download Instant Client Basic and ODBC package from http://www.oracle.com/technetwork/topics/intel-macsoft-096467.html
2. unzip instantclient-basic-macos.x64-12.2.0.1.0-2.zip and instantclient-odbc-macos.x64-12.2.0.1.0-2.zip
3. cd instantclient_12_2
4. update(edit) the driver name in odbc_update_ini.sh as follow:
   $ diff odbc_update_ini.sh.orig odbc_update_ini.sh
   101c101
   < SO_NAME=libsqora.so.12.1
   ---
   > SO_NAME=libsqora.dylib.12.1
5. ./odbc_update_ini.sh /usr/local
6. mkdir ~/lib
7. ln -s `pwd`/libclntsh.dylib.12.1 ~/lib
8. ln -s `pwd`/libclntshcore.dylib.12.1 ~/lib
9. ln -s `pwd`/libociei.dylib ~/lib
10. export PATH=`pwd`:$PATH
11. set ORACLE_SID, TNS_ADMIN or TWO_TASK variables as per your DB setup

Now verify the installation with isql:
isql OracleODBC-12c user password

dougk

Hi David,

I too need to get a Driver Manager working with the 12.2 Instant ODBC driver for Excel 2016 Oracle data sources.  As you have probably noticed, there is a huge documentation gap.  Oracle has not documented a working solution for installing any Driver Manager with their driver.  If you scroll back in this community a couple of weeks, you will see my posting and a bit of feedback received from Oracle,  I also opened an SR for this issue and am slowing working with support with the hopes that something positive will come of it.

I have tried three different driver managers and have had success with only one of them, and only from the command line, not from Excel.  Perhaps between the two of use we can figure this out.

1. ODBC Manager (http://www.odbcmanager.net) This "universal binary" does not seem to be fully 64-bit. Incompatible architecture error is raised when attempting to establish a connection when using this pre-built driver manager.

2. iODBC Driver Manager (http://www.iodbc.org/dataspace/doc/iodbc/wiki/iodbcWiki/Downloads#Mac%20OS%20X) Built this driver manager and discovered that it does not place an odbcinst.ini or odbc.ini file in any "etc" directory, which Oracle's odbc_update_ini script is looking for in the user specified driver manager directory. Worked around this by creating an etc directory and placing files in it. Had to correct some Oracle driver dependencies by creating a symbolic link in /usr/local/bin "ln -s libiodbcinst.2.dylib libodbcinst.2.dylib" for missing dependency and setting the DYLD_LIBRARY_PATH to include both /usr/local/lib and the Oracle ODBC path. Had to run otool against libsqora.dylib.12.1 multiple times to resolve these dependencies. Also had to change in Oracle's odbc_update_ini.sh script, the name of the driver file. Script has "libsqora.so.12.1", but the actual file is "libsqora.dynlib.12.1". After all of this setup, the driver could not establish a connection, always returning ORA-12154. SQLPlus had no trouble connecting to this database.

3. unixODBC Driver Manager (http://www.unixodbc.org/) Built this driver manager using "brew install unixodbc". Was able to successfully run Oracle's odbc_update_ini script and connect to the database from the command line using isql. This seemed encouraging, but Excel 2016 could not find a compatible driver manager. Something is missing.

Doug

dseverski

Hi, Doug.

I did see your earlier post and felt a kindred spirit there. The odbc manager part is pretty straightforward for me - unixODBC (also via homebrew) works for me for a host of drivers...except for the Oracle ODBC one of course. With the sqlplus client working, I'm reasonably confident that this is due to the @rpath linkages in the Oracle ODBC libs not being able to be followed. This seems like a pretty bog standard use case so I'm really hoping someone is able to demonstrate the "instant" part of the Instant Client for us and get us up and running!

Christopher Jones-Oracle

One buglet to workaround is to update odbc_update_ini.sh and change

SO_NAME=libsqora.so.12.1

to

SO_NAME=libsqora.dylib.12.1

Try that first.

Sdhamoth-Oracle

I'm able to install unixODBC & Instant Client ODBC driver on Mac OS and run ODBC applications.

I'm sharing the step by step procedure I followed to install unixODBC and Instant Client ODBC driver on Mac OS:

Installing unixODBC DM:

1) Download unixODBC-2.3.1.tar.gz  from ftp://ftp.unixodbc.org/pub/unixODBC/
2) tar -zxvf unixODBC-2.3.1.tar.gz
3) cd unixODBC-2.3.1
4) ./configure
5) ./make
6) ./make install

Installing Instant Client 12.2:

1. Download Instant Client Basic and ODBC package from http://www.oracle.com/technetwork/topics/intel-macsoft-096467.html
2. unzip instantclient-basic-macos.x64-12.2.0.1.0-2.zip and instantclient-odbc-macos.x64-12.2.0.1.0-2.zip
3. cd instantclient_12_2
4. update(edit) the driver name in odbc_update_ini.sh as follow:
   $ diff odbc_update_ini.sh.orig odbc_update_ini.sh
   101c101
   < SO_NAME=libsqora.so.12.1
   ---
   > SO_NAME=libsqora.dylib.12.1
5. ./odbc_update_ini.sh /usr/local
6. mkdir ~/lib
7. ln -s `pwd`/libclntsh.dylib.12.1 ~/lib
8. ln -s `pwd`/libclntshcore.dylib.12.1 ~/lib
9. ln -s `pwd`/libociei.dylib ~/lib
10. export PATH=`pwd`:$PATH
11. set ORACLE_SID, TNS_ADMIN or TWO_TASK variables as per your DB setup

Now verify the installation with isql:
isql OracleODBC-12c user password

dseverski

Thanks for the detailed steps! This did enable me to get isql working with the full Basic client. I notice that you're linking the libociei.dylib library from the full Basic client. That library isn't in the Basic Lite install, which I've been attempting to use. Is Basic Lite supported with ODBC and, if so, what library should be linked in to replace the libociei.dylib from the full client?

dougk

I was able to get unixODBC working as well using the Basic Lite Instant Client from the command line, but not with Excel.  I question whether unixODBC is intended to work with Excel?

Sdhamoth-Oracle

@dseverski, thank you for raising it. We identified step 9 and 10 are not required. i.e., we no need to have symbolic link to libociei.dylib in ~/.odbc.ini and include instantclient home directory in PATH (environment) variable.

Sdhamoth-Oracle

@dougk, I do not have hands on experience with Excel on Mac OS. But looks like there is native ODBC data connector in Excel on Mac. Did you check whether it support third party ODBC driver?

dougk

From experience, I know that the iODBC Driver Manager installs an interface for Excel, but I was unable to get that DM working with the 12.2 instant client ODBC driver.  Connection attempts always resulted in an ORA-12154 error, which shows that the driver was interfacing with the DM and hitting the database.   Will attempt to reinstall it again soon and will post the results.

Christopher Jones-Oracle

How/where were you setting the DB connection string?