Coding Architect

You never know what you can do till you try.

Installation of Oracle ODBC driver in Linux

Making Oracle ODBC driver working with Dyalog APL/Linux

There are two alternative ODBC implementations available on Linux:

You can read about differences between the projects in the article What are the functional differences between iODBC and unixODBC?

Which of them to choose? At the time of writing, the unixODBC is more actively developed than iODBC and unixODBC looks more popular as well.

My goal is to to get working Dyalog APL SQAPL on Linux with Oracle database. The SQAPL supports unixODBC, therefore I’m firm with my decision :)

Choice of Oracle ODBC drivers, available on Linux

There is number of Oracle ODBC driver implementations:

I choose the free Oracle Instant Client.

Installation of Oracle client

Here is the table with compatible versions of unixODBC Driver Manager and Instant Client:

Instant Client version unixODBC version O/S with the required unixODBC version
Instant Client 12.2 2.3.4 Ubuntu 18.04
Instant Client 12.1 2.3.1 Ubuntu 16.04
Instant Client 11g 2.2.11, 2.2.14 Ubuntu 14.04

The guidelines below are the “shortest path to success” which are compiled from articles which I have found. Some of the articles you may find in the References section.

I use Ubuntu 16.04 and therefore I download following packages of Instant Client 12.1 from Instant Client Downloads for Linux x86-64 (64-bit):

  • oracle-instantclient12.1-basiclite-12.1.0.2.0-1.x86_64.rpm - Oracle client libraries

  • oracle-instantclient12.1-odbc-12.1.0.2.0-1.x86_64.rpm - the ODBC driver

  • oracle-instantclient12.1-sqlplus-12.1.0.2.0-1.x86_64.rpm - SQL*Plus, it is needed only for troubleshooting and therefore the package be skipped

Install the downloaded packages and their dependencies:

$ sudo apt install alien libaio1
$ sudo alien -i oracle-instantclient12.1-*

Make the Oracle libraries available in LD_LIBRARY_PATH and initialize system-wide ORACLE_HOME:

$ echo /usr/lib/oracle/12.1/client64/lib/ | sudo tee  /etc/ld.so.conf.d/oracle.conf && sudo chmod o+r /etc/ld.so.conf.d/oracle.conf
$ sudo ldconfig
$ echo 'export ORACLE_HOME=/usr/lib/oracle/12.1/client64' | sudo tee /etc/profile.d/oracle.sh && sudo chmod o+r /etc/profile.d/oracle.sh
$ . /etc/profile.d/oracle.sh

Ensure all library references are resolved for SQL*Plus:

$ ldd `which sqlplus64`
        linux-vdso.so.1 =>  (0x00007ffc77593000)
        libsqlplus.so => /usr/lib/oracle/12.1/client64/lib/libsqlplus.so (0x00007f3cf0112000)
        libclntsh.so.12.1 => /usr/lib/oracle/12.1/client64/lib/libclntsh.so.12.1 (0x00007f3ced155000)
        libclntshcore.so.12.1 => /usr/lib/oracle/12.1/client64/lib/libclntshcore.so.12.1 (0x00007f3cecbe3000)
        libmql1.so => /usr/lib/oracle/12.1/client64/lib/libmql1.so (0x00007f3cec96d000)
        libipc1.so => /usr/lib/oracle/12.1/client64/lib/libipc1.so (0x00007f3cec5ef000)
        libnnz12.so => /usr/lib/oracle/12.1/client64/lib/libnnz12.so (0x00007f3cebee5000)
        libdl.so.2 => /lib/x86_64-linux-gnu/libdl.so.2 (0x00007f3cebce1000)
        libm.so.6 => /lib/x86_64-linux-gnu/libm.so.6 (0x00007f3ceb9d8000)
        libpthread.so.0 => /lib/x86_64-linux-gnu/libpthread.so.0 (0x00007f3ceb7bb000)
        libnsl.so.1 => /lib/x86_64-linux-gnu/libnsl.so.1 (0x00007f3ceb5a2000)
        librt.so.1 => /lib/x86_64-linux-gnu/librt.so.1 (0x00007f3ceb39a000)
        libc.so.6 => /lib/x86_64-linux-gnu/libc.so.6 (0x00007f3ceafd0000)
        libons.so => /usr/lib/oracle/12.1/client64/lib/libons.so (0x00007f3cead8b000)
        /lib64/ld-linux-x86-64.so.2 (0x00007f3cf040a000)
        libaio.so.1 => /lib/x86_64-linux-gnu/libaio.so.1 (0x00007f3ceab89000)

Test your database connection using SQL*Plus. The successful session should look like this:

$ sqlplus64 username/password@//dbhost:1521/SID

SQL*Plus: Release 12.1.0.2.0 Production on Mon Nov 26 08:31:29 2018

Copyright (c) 1982, 2014, Oracle.  All rights reserved.

Last Successful login time: Sun Nov 25 2018 19:40:35 -08:00

Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

SQL> SELECT COUNT(*) FROM DUAL;

  COUNT(*)
----------
         1

If you don’t plan to use SQL*Plus in the future, you may safely remove package oracle-instantclient12.1-sqlplus without affecting your ODBC setup.

Installation of unixODBC

Install unixODBC and ensure all library dependencies of the Oracle ODBC driver are resolved:

$ sudo apt install unixodbc
$ ldd /usr/lib/oracle/12.1/client64/lib/libsqora.so.12.1
        linux-vdso.so.1 =>  (0x00007fff5d39b000)
        libdl.so.2 => /lib/x86_64-linux-gnu/libdl.so.2 (0x00007fc959b08000)
        libm.so.6 => /lib/x86_64-linux-gnu/libm.so.6 (0x00007fc9597ff000)
        libpthread.so.0 => /lib/x86_64-linux-gnu/libpthread.so.0 (0x00007fc9595e2000)
        libnsl.so.1 => /lib/x86_64-linux-gnu/libnsl.so.1 (0x00007fc9593c9000)
        librt.so.1 => /lib/x86_64-linux-gnu/librt.so.1 (0x00007fc9591c1000)
        libclntsh.so.12.1 => /usr/lib/oracle/12.1/client64/lib/libclntsh.so.12.1 (0x00007fc956204000)
        libodbcinst.so.2 => /usr/lib/x86_64-linux-gnu/libodbcinst.so.2 (0x00007fc955ff2000)
        libc.so.6 => /lib/x86_64-linux-gnu/libc.so.6 (0x00007fc955c28000)
        /lib64/ld-linux-x86-64.so.2 (0x00007fc95a17d000)
        libmql1.so => /usr/lib/oracle/12.1/client64/lib/libmql1.so (0x00007fc9559b2000)
        libipc1.so => /usr/lib/oracle/12.1/client64/lib/libipc1.so (0x00007fc955634000)
        libnnz12.so => /usr/lib/oracle/12.1/client64/lib/libnnz12.so (0x00007fc954f2a000)
        libons.so => /usr/lib/oracle/12.1/client64/lib/libons.so (0x00007fc954ce5000)
        libaio.so.1 => /lib/x86_64-linux-gnu/libaio.so.1 (0x00007fc954ae3000)
        libclntshcore.so.12.1 => /usr/lib/oracle/12.1/client64/lib/libclntshcore.so.12.1 (0x00007fc954571000)
        libltdl.so.7 => /usr/lib/x86_64-linux-gnu/libltdl.so.7 (0x00007fc954367000)

Initialize TNS_ADMIN and populate tnsnames.ora.

$ echo 'export TNS_ADMIN=$ORACLE_HOME/network/admin' | sudo tee -a /etc/profile.d/oracle.sh
$ . /etc/profile.d/oracle.sh

$ sudo mkdir -p $ORACLE_HOME/network/admin
$ cat | sudo tee $ORACLE_HOME/network/admin/tnsnames.ora
# paste tnsnames content here and finish with Ctrl-D

Look where unixODBC expects to find configuration files:

$ odbcinst -j
unixODBC 2.3.1
DRIVERS............: /etc/odbcinst.ini
SYSTEM DATA SOURCES: /etc/odbc.ini
FILE DATA SOURCES..: /etc/ODBCDataSources
USER DATA SOURCES..: /home/vagrant/.odbc.ini
SQLULEN Size.......: 8
SQLLEN Size........: 8
SQLSETPOSIROW Size.: 8

Configure /etc/odbcinst.ini as:

[Oracle 12c ODBC driver]
Description     = Oracle ODBC driver for Oracle 12c
Driver          = /usr/lib/oracle/12.1/client64/lib/libsqora.so.12.1
Setup           =
FileUsage       =
CPTimeout       =
CPReuse         =

Configure /etc/odbc.ini as:

[OracleODBC-12c]
Application Attributes = T
Attributes = W
BatchAutocommitMode = IfAllSuccessful
BindAsFLOAT = F
CloseCursor = F
DisableDPM = F
DisableMTS = T
Driver = Oracle 12c ODBC driver
DSN = OracleODBC-12c
EXECSchemaOpt =
EXECSyntax = T
Failover = T
FailoverDelay = 10
FailoverRetryCount = 10
FetchBufferSize = 64000
ForceWCHAR = F
Lobs = T
Longs = T
MaxLargeData = 0
MetadataIdDefault = F
QueryTimeout = T
ResultSets = T
ServerName = <!!your TNS name goes here!!>
SQLGetData extensions = F
Translation DLL =
Translation Option = 0
DisableRULEHint = T
UserID =
StatementCache=F
CacheBufferSize=20
UseOCIDescribeAny=F
SQLTranslateErrors=F
MaxTokenSize=8192
AggregateSQLType=FLOAT

Test the ODBC connection with unixODBC client:

$ isql "OracleODBC-12c" <dbuser> <dbpassword> -v
+---------------------------------------+
| Connected!                            |
|                                       |
| sql-statement                         |
| help [tablename]                      |
| quit                                  |
|                                       |
+---------------------------------------+
SQL> SELECT COUNT(*) FROM DUAL;
+-----------------------------------------+
| COUNT(*)                                |
+-----------------------------------------+
| 1                                       |
+-----------------------------------------+
SQLRowCount returns -1
1 rows fetched
SQL>

If your output looks like this, then the ODBC part is done.

In my next article I describe how to make the Dyalog APL work with the unixODBC.

References