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

      • 1. Re: Oracle 12.2 ODBC driver installation on MacOS with unixodbc
        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

        • 2. Re: Oracle 12.2 ODBC driver installation on MacOS with unixodbc
          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!

          • 3. Re: Oracle 12.2 ODBC driver installation on MacOS with unixodbc
            cj

            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.

            • 4. Re: Oracle 12.2 ODBC driver installation on MacOS with unixodbc
              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

              • 5. Re: Oracle 12.2 ODBC driver installation on MacOS with unixodbc
                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?

                • 6. Re: Oracle 12.2 ODBC driver installation on MacOS with unixodbc
                  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?

                  • 7. Re: Oracle 12.2 ODBC driver installation on MacOS with unixodbc
                    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.

                    • 8. Re: Oracle 12.2 ODBC driver installation on MacOS with unixodbc
                      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?

                      • 9. Re: Oracle 12.2 ODBC driver installation on MacOS with unixodbc
                        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.

                        • 10. Re: Oracle 12.2 ODBC driver installation on MacOS with unixodbc
                          cj

                          How/where were you setting the DB connection string?