X

The leading edge of scripting languages and Oracle Database brought to you by the Data Access Development team

Installing the Oracle ODBC Driver on macOS

Christopher Jones
Senior Principal Product Manager

A bonus for today is a guest post by my colleague Senthil Dhamotharan. He shares the steps to install the Oracle Instant Client ODBC driver and the unixODBC Driver Manager on macOS.

ODBC is an open specification for accessing databases. The Oracle ODBC driver for Oracle Database enables ODBC applications to connect to Oracle Database. In addition to standard ODBC functions, users can leverage Oracle specific features for high performance data access.

Install the unixODBC Driver Manager

  • Download unixODBC from ftp.unixodbc.org/pub/unixODBC. I used unixODBC-2.3.1.tar.gz.

  • Open a Terminal and extract the package:

    tar -zxvf unixODBC-2.3.1.tar.gz
  • Configure unixODBC:

    cd unixODBC-2.3.1
    ./configure
    

    Note if you use the configure option "--prefix" to install into locations other than the default directory (/usr/local) then macOS's SIP features may prevent the unixODBC libraries being located correctly by the ODBC driver.

  • Build and install unixODBC:

    make
    sudo make install
    

Install the Oracle ODBC Driver

  • Download the Oracle 12.2 Instant Client Basic and ODBC packages from Instant Client Downloads for macOS (Intel x86).

    To reduce the installation size, the Basic Light package be used instead of Basic, if its character sets and languages are sufficient.

  • Extract both ZIP files:

    unzip instantclient-basic-macos.x64-12.2.0.1.0-2.zip
    unzip instantclient-odbc-macos.x64-12.2.0.1.0-2.zip
    

    This will create a subdirectory instantclient_12_2

  • The Oracle Instant Client libraries need to be in the macOS library search path, generally either in /usr/lib/local or in your home directory under ~/lib. I did:

    mkdir ~/lib
    cd instantclient_12_2
    ln -s $(pwd)/libclntsh.dylib.12.1 $(pwd)/libclntshcore.dylib.12.1 ~/lib
    
  • With version 12.2, a small patch to the driver name in instantclient_12_2/odbc_update_ini.sh is required on macOS. I changed line 101 from:

    SO_NAME=libsqora.so.12.1
    

    to

    SO_NAME=libsqora.dylib.12.1
    
  • Run the configuration script

    cd instantclient_12_2
    sudo odbc_update_ini.sh /usr/local
    sudo chown $USER ~/.odbc.ini
    

    This creates a default DSN of "OracleODBC-12c"

  • Edit the new ~/.odbc.ini configuration file and add the Oracle Database connection string. My database is running on the same machine as ODBC (inside a VirtualBox VM) and has a service name of 'orclpdb', so my connection string is 'localhost/orclpdb'. I changed:

    ServerName =
    

    to

    ServerName = localhost/orclpdb
    

Verify the installation

Run the isql utility to verify installation. Pass in the DSN name, and an existing database username and password:

$ isql OracleODBC-12c scott tiger

+---------------------------------------+
| Connected!                            |
|                                       |
| sql-statement                         |
| help [tablename]                      |
| quit                                  |
|                                       |
+---------------------------------------+

SQL>

You can execute SQL statements and quit when you are done.

Test Program

To test a program that makes ODBC calls, download odbcdemo.c.

  • Edit odbcdemo.c and set the USERNAME and PASSWORD constants to the database credentials.

  • Build it:

    gcc -o odbcdemo -g -lodbc odbcdemo.c
    
  • Run it

    ./odbcdemo
    

The output will be like:

Connecting to the DB .. Done
Executing SQL ==> SELECT SYSDATE FROM DUAL
Result        ==> 2018-02-21 02:53:47

Summary

ODBC is a popular API for accessing databases. The Oracle ODBC Driver is the best way to access Oracle Database.

Resources

Using the Oracle ODBC Driver.

Oracle ODBC Drivers

Discussion Forum

Oracle Instant Client ODBC Release Notes

Instant Client Downloads

Join the discussion

Comments ( 15 )
  • Matthew Johnson Thursday, June 7, 2018
    Thanks for this.. I have followed these instructions

    I have a ORACLE LOGIN string of my database:
    user/password@myhost.com:1521/RED
    that works with
    'sqlplus user/password@myhost.com:1521/RED'

    putting 'ServerName = myhost.com:1521/RED' into ~/.odbc.ini and trying

    'isql -v mydsn user password' results in:

    Errors in file :
    OCI-21500: internal error code, arguments: [17114], [0x7FD87F81A180], [], [], [], [], [], []


    ----- Call Stack Trace -----
    calling call entry argument values in hex
    location type point (? means dubious value)
    -------------------- -------- -------------------- ----------------------------
    and a hung process

    any ideas?
  • Christopher Jones Thursday, June 7, 2018
    Make sure you have the latest Instant Client (the Mac onese were recut once).

    Then post a testcase with details to the help forum https://community.oracle.com/community/database/developer-tools/windows_and_.net/odbc
  • Matthew Johnson Thursday, June 7, 2018
    That worked with latest client! Thank you!
  • Christopher Jones Friday, June 8, 2018
    You're welcome. Thanks for letting me know.
  • Juan Tuesday, August 14, 2018
    Done all the steps in my mac but no luck: Message:

    [S1000][unixODBC][Oracle][ODBC][Ora]ORA-12514: TNS:listener does not currently know of service requested in connect descriptor

    I'm using localhost/ee in my .odbc.ini file. I connect by using sqlDeveloper and SID=ee and works fine.

    Any help?
  • Christopher Jones Wednesday, August 15, 2018
    The best place to continue a technical support request is the forum: https://community.oracle.com/community/database/developer-tools/windows_and_.net/odbc

    Don't forget that a SID is not a service name.
  • Juan Wednesday, August 15, 2018
    Thank you Christopher. I'll take a look in the forum. If I don't use Service name but SID, what does it mean? as far as I know Service Name is a DB alias and SID refers to the database name. Am I wrong?

    Thanks again,

    Best,
  • juan Saturday, August 18, 2018
    Hello Christophe. I made it works by using SERVICE_NAME instead of SID getting it from the database with the following SQL:
    select value from v$parameter where name like '%service_name%'

    Thanks for your help, it drove me to the solution.

    Best!

    Juan
  • Christopher Jones Monday, August 20, 2018
    I'm glad you got it sorted. Thanks for letting me know.
  • Lani Tuesday, January 29, 2019
    Do i have to type this in terminal or not ?
  • lani Tuesday, January 29, 2019
    Where do I configure this in mac terminal ? or does it need an app? thank you
  • Christopher Jones Tuesday, January 29, 2019
    @ lani: yes, use a terminal.
  • Tom Friday, February 15, 2019
    Under "Install the Oracle ODBC Driver", when you say:

    Extract both ZIP files:

    unzip instantclient-basic-macos.x64-12.2.0.1.0-2.zip
    unzip instantclient-odbc-macos.x64-12.2.0.1.0-2.zip

    This will create a subdirectory instantclient_12_2

    What directory are you assuming we're in when we create the subdirectory? Does it matter to the rest of the instructions? (I created ~/Oracle, and I'm creating the subdirectory under there.)
  • Christopher Jones Friday, February 15, 2019
    It shouldn't matter.
  • joe wolk Monday, February 18, 2019
    I have a excel spreadsheet that has macros that reach es out to oracle for data running on a windows laptop.I wish to also run it on the macbook pro version of Excel 2016 and I am wondering if its possible after adding the oracle odbc driver on the mac??
Please enter your name.Please provide a valid email address.Please enter a comment.CAPTCHA challenge response provided was incorrect. Please try again.Captcha