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.
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!
One buglet to workaround is to update odbc_update_ini.sh and change
Try that first.
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
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-22.214.171.124.0-2.zip and instantclient-odbc-macos.x64-126.96.36.199.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
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
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?
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?
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.
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.
How/where were you setting the DB connection string?