Connect to Oracle from Linux or Mac with ODBC

Introduction

This article explains how to connect to Oracle using unixODBC and the Oracle Connector/ODBC drivers. It includes installation and configuration instructions for Ubuntu, CentOS and the Mac. We strongly recommend using Oracle Easy Connect (EZCONNECT) instead because it is much easier to setup.

It covers the following topics:

  1. How to connect to Oracle (with code samples).
  2. How to setup ODBC:
    • Install unixODBC:

      This is needed because to manage ODBC on Linux. The unixODBC install also include utilities isql and iusql.

    • Install Connector/ODBC:

      This is the official ODBC driver manager and driver from the Oracle website. You can configure ODBC using only odbc.ini, which contains the ODBC DSN details. You can optionally using odbcinst.ini as well but it is not needed.

    • Configure ODBC using the odbc.ini file:

      We recommend using system DSNs (available to all users), which use the /usr/odbc.ini file. User DSNs can be setup in the $HOME/.odbc.ini file.

Note: Oracle Easy Connect (EZCONNECT) is much easier to setup — we strongly recommend Easy Connect unless your DBA (database administrator) says otherwise. According to Oracle Easy Connect is not recommended for complex systems.

Quote from the Oracle documentation: “Easy Connect naming is not suitable for large or complex environments with advanced features, such as connection pooling, external procedure calls, or Heterogeneous Services, that require additional connect information. In these cases, another naming method is recommended.”

Connect to Oracle [top]

We strongly recommend using connection objects to connect to databases, and this is what we demonstrate in the code samples. The code samples will work with all operating systems.

Follow these steps to connect:

  1. Create a new channel to try out the code:
    • Create a channel like this:
      • Name: DB Oracle Connection (or similar)
      • Source: From Translator
      • Destination: To Channel
    • Alternatively use an existing channel:

      Your channel will need a From/To Translator or a Filter component.

  2. Connect to a database using ODBC:
    • Paste this code into the Translator:

      If your script uses connection object methods conn:query{} or conn:execute{}to connect to a remote Oracle database, the location of the remote host is specified as part of the name parameter in the db.connect{ } command. If you use conn:merge{} to connect the remote host is specified as part of the name parameter in the conn:merge{} command itself.

      function main()
            local conn = db.connect{   
            api=db.ORACLE_ODBC, 
            name='your_server_name',
            user='your_login', 
            password='secret',
            use_unicode = true,
            live = true
         }
      
         conn:execute{sql='SELECT * FROM <your table>', live=true}
      end
    • The name is the same as the server entry in the odbc.ini file.
  3. Adapt the code to your requirements.

Setup ODBC [top]

Note: Iguana may not always support the very latest version of Oracle drivers, so you can simply download an earlier version if necessary. This is particularly true if you are not able to use the the latest version of Iguana.

If you need to know which versions of Oracle drivers are supported by the version of Iguana you are using please contact us at support@interfaceware.com.

Linux:

Note: The procedure and screenshots in this section are from Ubuntu 18.04 — and will differ somewhat if you are using a different distributions of Linux.

To set up a new ODBC data source for Oracle Server:

  1. Setup the Oracle instant client instant client OCI drivers:

    The Oracle ODBC package depends on the OCI drivers — so you need to set them up first.

    • Follow the same procedure used for Oracle Easy Connect: Setup the Linux Oracle OCI drivers.
    • Alternatively: You can run this terminal command to check the version of previously instant client drivers:

      This can be useful if the OCI instant client is already installed and you just want to confirm which version is used. You can tell the from the name of the instant client install directory.

      find /usr -iname instantclient*

      You will see something like this:
      find instant client install directory

  2. Install the Oracle ODBC drivers:
    1. Download the ODBC zip package from the Oracle instant client download site:
      • Choose the same version of the ODBC drivers as the OCI drivers from step (1) above
    2. Unzip the ODBC zip package into the /usr/lib/instantclient_X_Y folder where you installed the OCI drivers:
      1. Terminal Command: Unzip the files:
        Note: Replace <version> to match the file you downloaded.

        sudo unzip ~/Downloads/instantclient-odbc-linux.x64-<version>.zip -d /usr/lib
      2. Check that the ODBC driver files were created in the instantclient_X_Y folder:
        • Terminal Command: Check the files
          Note: Replace X_Y to match the your install directory name.

          ls -l /usr/lib/instantclient_X_Y/odbc*
        • You should see something like this:
          confirm odbc install
      3. Create a symbolic link for the ODBC driver”
        1. Terminal Command: Create a symbolic link
          Note: Replace X_Y to match the your install directory name.

          sudo ln -s /usr/lib/instantclient_X_Y/libsqora.so.18.1 /usr/lib/instantclient_X_Y/libsqora.so
        2. Terminal Command: Check the link was created:
          Note: Replace X_Y to match the your install directory name.

          ls -l /usr/lib/instantclient_X_Y/libsqora.so

          You should see something like this:
          confirm link

    3. Install the Oracle instant client ODBC:
      1. Terminal Command: Check the home directory of unixODBC:

        The home directory is where where the odbcinst.ini and the global/system odbc.ini reside.

        odbcinst -j
      2. In this case the home directory is the default /etc:
        check for unixodbc hoem directory
      3. Terminal Command: Install ODBC:

        The second command assumes that the unixODBC home directory is the default “/etc” — if your home directory is elsewhere then read the warning below.

        cd /usr/lib/instantclient*
        sudo ./odbc_update_ini.sh /

        Warning: The second command works with the ODBC install script (odbc_update_ini.sh) supplied by Oracle when this article was written. If the quirky (incorrect) behaviour of the script is modified (fixed) the second command will need to be modified.

        The second command runs the ODBC install script (odbc_update_ini.sh) supplied by Oracle. The parameter (“/”) refers to the “home directory” for unixODBC, which by default is “/etc”. The parameter should be “/etc”, however this fails because the script appends “/etc” to the path so you need to use “/” instead.

        Note: If your home directory is elsewhere that does not end in “/etc” then the current script cannot work without modification — you would need to modify the install script so it does not append “/etc” to the supplied path parameter.

  3. Identify the TNS Alias that identifies your database:

    Oracle uses a TNS Alias for the Servername parameter — whereas most other databases use the network name of the Database Server. If you use the Database Server name your ODBC connection will not work.

    • Get a tnsnames.ora file with the correct tns_alias entry from your DBA.
    • Alternatively you can create your own tns_alias, see Setup the OCI Connection for details.
  4. Create the odbc.ini file entries:

    Be aware that the ODBC parameters in odbc.ini entries can vary for different databases. In particular Oracle and PostgreSQL use Servername to identify the server name, whereas many other databases simply use Server (for example Microsoft SQL Server and MySQL). It is important to use the correct parameters for your database or the ODBC connection will not work correctly.

    1. Format your entries like this:
      • Bare minimum odbc.ini entry:
        Note: Change [My Oracle ODBC connection name] to to the name you prefer for your ODBC connection.

        [My Oracle ODBC connection name]
        Driver     = /usr/lib/instantclient_X_Y/libsqora.so
        Servername = tns-alias
        Port       = 1521
      • Entry with user details etc:
        Note: The <comments> in angle brackets should not be included

        [My Oracle ODBC connection name]
        Driver     = /usr/lib/instantclient_X_Y/libsqora.so
        Servername = tns-alias <this is a tns-alias entry from your tnsnames.ora file>
        Port       = 1521 <1521 = default – a different port can be used>
        Database   = dbname <if omitted the default database will be used>
        Username   = dbusername <if omitted then integrated security is used – needs kerberos>
        Password   = dbpassword
  5. Check the location of ODBC ini files on your system:
    1. Terminal Command: Check the locations:
      odbcinst -j
    2. In this case the ODBC ini files are in the default locations:
      • System ODBC ini file: /etc/odbc.ini
      • User ODBC ini file: is in the user home directory (/home/parallels/.odbc.ini)
        odbc ini locations
  6. Append your entries to the ODBC ini file:

    We generally recommend creating System DSNs (available to all users) rather than User DSNs (only available to the current user).

    1. Create a System DSN:
      1. Append your ODBC entry to the System ini file you identified step (4)
      2. Open the file with a text editor and paste your entry at the end.
        Note: You need administrator permission to edit this file.
      3. Alternatively: Use the unixODBC odbcinst utility to append your entry:
        1. Save your entry to a text file (unixODBC calls this as a “template file”)
        2. Terminal Command: Append the entry from your template file:
          odbcinst -i -d -l -f template_file
    2. Create a User DSN:
      1. Append your ODBC entry to the User ini file you identified step (4)
      2. Open the file with a text editor and paste your entry at the end.
      3. Alternatively: Use the unixODBC odbcinst utility to append your entry:
        1. Save your entry to a text file (unixODBC calls this as a “template file”)
        2. Terminal Command: Append the entry from your template file:
          odbcinst -i -d -h -f template_file
    3. An ODBC ini file will look something like this after a new entry is added:
      new odbc.ini oracle entry
  7. If the connection does not work speak to your DBA (database administrator) to troubleshoot the settings.

Mac:

Note: The procedure and screenshots in this section are from macOS Mojave — and may differ somewhat if you are using a different version of macOS.

  1. Setup the Oracle instant client instant client OCI drivers:

    The Oracle ODBC package depends on the OCI drivers — so you need to set them up first.

    • Follow the same procedure used for Oracle Easy Connect: Setup the Mac Oracle OCI drivers.
    • Alternatively: You can run this terminal command to check the version of previously instant client drivers:

      This can be useful if the OCI instant client is already installed and you just want to confirm which version is used. You can tell the from the name of the instant client install directory.

      sudo find /usr -iname instantclient*

      You will see something like this (in this case version 12.2):
      find installed instatn client version

  2. Install the Oracle ODBC drivers:
    1. Download the ODBC zip package from the Oracle instant client download site:
      • Choose the same version of the ODBC drivers as the OCI drivers from step (1) above
    2. Terminal Command: Unzip the ODBC package:

      This command will unzip the package into the default/recommended /usr/local/instantclient_X_Y directory. If you installed instant client elsewhere you need change the command to: sudo unzip ~/downloads/instantclient-odbc-macos.x64-<version>.zip -d <parent directory of instantclient_X_Y>

      Note: Change <version> to match the name of your download.

      sudo unzip ~/downloads/instantclient-odbc-macos.x64-<version>.zip -d /usr/local
    3. You should know see the ODBC driver files in /usr/local/instantclient_X_Y, something like this:

      The /usr/local directory is hidden in Finder, so use Go>Go to Folder and enter “/usr/local” to view it.

      view odbc drivers

    4. Create a symbolic link to the ODBC dynamic library in /usr/local/lib:
      1. Terminal Command: Create symbolic link:
        Note: replace “<_X_Y>” and “X.Y” with the version numbers to match your instant client version.

        If you are upgrading to a new version of ODBC you will need to delete the old symbolic link and recreate it to point to the new ODBC library.

        sudo ln -s /usr/local/instantclient<_X_Y>/libsqora.dylib.<X.Y> /usr/local/lib/libsqora.dylib
      2. The symbolic link should look like this:
        oracle library shortcut
  3. Setup the unixODBC driver manager:

    We need to use unixODBC because the default Apple driver manager does not work with some databases, see Setup unixODBC on the Mac for more information.

  4. Create the odbc.ini file entries:

    Be aware that the ODBC parameters in odbc.ini entries can vary for different databases. In particular MySQL and Microsoft SQL Server use Server to identify the server name, whereas some other databases use Servername (for example Oracle and PostgreSQL). It is important to use the correct parameters for your database or the ODBC connection will not work correctly.

    1. Format your entries like this:
      • Bare minimum odbc.ini entry:
        Note: Change [My Oracle ODBC connection name] to the name you prefer for your ODBC connection.

        [My Oracle ODBC connection name] 
        Driver = /usr/local/lib/libsqora.dylib
        Server = your_server_name 
        Port   = 1521
      • Entry with user details etc:
        Note: The <comments> in angle brackets should not be included.

        [My Oracle ODBC connection name] 
        Driver = /usr/local/lib/libsqora.dylib
        Server = your_server_name <network name or IP address for the server> 
        Port = 1521 <1521 = default - a different port can be used> 
        Database = dbname <if omitted the default database will be used> 
        Username = dbusername <if omitted then integrated security is used - needs kerberos> 
        Password = dbpassword
  5. Check the location of ODBC ini files on your system:
    1. Terminal Command: Check the locations:
      odbcinst -j
    2. In this case the ODBC ini files are in the default locations:
      • System ODBC ini file: /usr/local/etc/odbc.ini
      • User ODBC ini file: is in the user home directory (/Users/julianmuir/.odbc.ini):
        odbcinst -j
  6. Append your entries to the ODBC ini file:

    We generally recommend creating System DSNs (available to all users) rather than User DSNs (only available to the current user).

    1. Create a System DSN:
      1. Append your ODBC entry to the System ini file you identified in step (5)
      2. Open the file with a text editor and paste your entry at the end.
      3. Alternatively: Use the unixODBC odbcinst utility to append your entry:
        1. Save your entry to a text file (unixODBC calls this as a “template file”)
        2. Terminal Command: Append the entry from your template file:

          odbcinst -i -d -l -f template_file

    2. Create a User DSN:
      1. Append your ODBC entry to the User ini file you identified step (5)
      2. Open the file with a text editor and paste your entry at the end.
      3. Alternatively: Use the unixODBC odbcinst utility to append your entry:
        1. Save your entry to a text file (unixODBC calls this as a “template file”)
        2. Terminal Command: Append the entry from your template file:

          odbcinst -i -d -h -f template_file

    3. An ODBC ini file will look something like this after a new entry is added:
      oddb.ini new entry
  7. If you have problems configuring the ODBC driver or connection speak to your DBA (database administrator).

Troubleshooting [top]

  • Problems with connection settings:

    Speak to your DBA (database administrator) for general connection issues like: User name and password, Database Server network name etc.

  • Iguana may not always support the very latest version of the ODBC drivers:

    Please contact us at support@interfaceware.com if you need to know the latest supported version of the ODBC drivers.

    Incorrect port number:

    SQL Server defaults to port 1521, but a different port can be used. Speak to your database administrator (DBA) or network administrator.

  • Using integrated security:

    Kerberos is needed on Linux, this SQL Server page Using Integrated Authentication has some information

  • Versioned Packages:

    When a package installs a shared library, it can append a version number, so double check the actual library names and if needed symlink them to the library names shown in the previous section (libtdsodbc.so & libtdsS.so), for example:

    sudo ln -s /usr/lib/x86_64-linux-gnu/libodbc.so.1 /usr/lib/x86_64-linux-gnu/libodbc.so

If you need more help please contact us at support@interfaceware.com.

More Information [top]

Leave A Comment?