Oracle Database - ODBC Driver Insallation on Linux

> Database > Oracle Database

1 - About

How to install the ODBC Driver of Oracle on Linux (unixODBC)

3 - Steps

3.1 - Driver manager installation

Install driver manager: ODBC - Driver Manager

Prerequisites: Verify the driver manager version.

Instant Client Version (unixODBC|Driver Manager) version
12c 2.3.1
11c 2.2.11 (or 2.2.14 for Linux 64bit)

See Oracle Instant Client ODBC Release Notes

Advertising

3.2 - Download (driver shared library)

Download:

  • oracle-instantclient-basic-linux.x64-11.2.0.4.0.zip (provides the core)
  • oracle-instantclient-sqlplus-linux.x64-11.2.0.4.0.zip (provides sqlplus)
  • oracle-instantclient-odbc-linux.x64-11.2.0.4.0.zip, (provides odbc)
  • oracle-instantclient-devel-linux.x64-11.2.0.4.0.zip (for dev, not needed ??)

in a temp directory. Example: /u01/app/oracle/

and unzip them

unzip *.zip

The ODBC driver shared library file are:

  • For 12c: libsqora.so.12.1
  • For 11g: libsqora.so.11.1

3.3 - Add the library in the library path

Add the library in the ld_library_path

export LD_LIBRARY_PATH=$LD_LIBRARY_PATH:/u01/app/oracle/instantclient_11_2
Advertising

3.4 - Define the TNS_ADMIN environment variable

3.5 - Test Oracle Driver installation

dltest /u01/app/oracle/instantclient_11_2/libsqora.so.11.1
#SUCCESS: Loaded /usr/lib/oracle/11.2/client64/lib/libsqora.so.11.1

3.5.1 - file not found ?

If it doesn't work with [dltest] ERROR dlopen: file not found you miss a dependency library in your LD_LIBRARY_PATH. You can see it with the ldd tool

ldd /u01/app/oracle/instantclient_12_1/libsqora.so.12.1
  • The libodbcinst.so.2 is not found.
        linux-vdso.so.1 =>  (0x00007fff165ff000)
        libdl.so.2 => /lib64/libdl.so.2 (0x00007f3e7a549000)
        libm.so.6 => /lib64/libm.so.6 (0x00007f3e7a2c5000)
        libpthread.so.0 => /lib64/libpthread.so.0 (0x00007f3e7a0aa000)
        libnsl.so.1 => /lib64/libnsl.so.1 (0x00007f3e79e92000)
        librt.so.1 => /lib64/librt.so.1 (0x00007f3e79c88000)
        libclntsh.so.12.1 => /u01/app/oracle/instantclient_12_1/libclntsh.so.12.1 (0x00007f3e76ccb000)
        libodbcinst.so.2 => not found
        libc.so.6 => /lib64/libc.so.6 (0x00007f3e76972000)
        /lib64/ld-linux-x86-64.so.2 (0x00000035bb600000)
        libmql1.so => /u01/app/oracle/instantclient_12_1/libmql1.so (0x00007f3e766fc000)
        libipc1.so => /u01/app/oracle/instantclient_12_1/libipc1.so (0x00007f3e7637e000)
        libnnz12.so => /u01/app/oracle/instantclient_12_1/libnnz12.so (0x00007f3e75c73000)
        libons.so => /u01/app/oracle/product/fmw/Oracle_BI1/lib/libons.so (0x00007f3e75b3a000)
        libaio.so.1 => /usr/lib64/libaio.so.1 (0x00007f3e75939000)
        libclntshcore.so.12.1 => /u01/app/oracle/instantclient_12_1/libclntshcore.so.12.1 (0x00007f3e753c6000)

Create the appropriate libclntsh.so and libocci.so links for the version of Instant Client

cd /u01/app/oracle/instantclient_11_2 # Install dir
ln -s libclntsh.so.11.1 libclntsh.so
ln -s libocci.so.11.1 libocci.so
Advertising

3.7 - Add the ODBC configuration

Oracle provides an utility to do it for you:

odbc_update_ini.sh <ODBCDM_Home> [<Install_Location> <Driver_Name> <DSN> <ODBCINI>]

where:

  • ODBCDM_Home unixODBC driver manager home directory path (Mandatory)
  • Install_Location* Oracle Instant Client directory path. Default path is current directory (./)
  • Driver_Name* Driver name to identify the Oracle ODBC driver resides in current Oracle Instant Client home. Default name is “Oracle 12c ODBC driver” for Oracle 12c Instant Client
  • DSN* Sets ODBC DSN name. Default name is “OracleODBC-12c” for Oracle 12c Instant Client
  • ODBCINI* Directory path of the .odbc.ini file. The default path is the user's home directory (~/.odbc.ini)

Example:

# located in the odbc oracle download file. First unzip
sudo ./odbc_update_ini.sh "/usr/local"

It will update:

  • <DM-HOME>/etc/odbcinst.ini where DM-HOME is the data manager home (locate odbcinst)
  • and ~/.odbc.ini

3.7.1 - Drivers

Example

cat /usr/local/etc/odbcinst.ini
[Oracle 11g ODBC driver]
Description     = Oracle ODBC driver for Oracle 11g
Driver          = /u01/app/oracle/instantclient_11_2/libsqora.so.11.1
Setup           =
FileUsage       =
CPTimeout       =
CPReuse         =

3.7.2 - DSN

cat ~/.odbc.ini
  • Minimal parameters
[Dsn]
Driver = /u01/app/oracle/instantclient_12_1/libsqora.so.12.1
ServerName = TNS_NAME
  • Complete:
[OracleODBC-11g]
Application Attributes = T
Attributes = W
BatchAutocommitMode = IfAllSuccessful
BindAsFLOAT = F
CloseCursor = F
DisableDPM = F
DisableMTS = T
Driver = Oracle 11g ODBC driver
DSN = OracleODBC-11g
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 =
SQLGetData extensions = F
Translation DLL =
Translation Option = 0
DisableRULEHint = T
UserID =
StatementCache=F
CacheBufferSize=20
UseOCIDescribeAny=F

3.8 - Test it

with unixODBC - isql

isql -v OracleODBC-11g

4 - Support

4.1 - undefined symbol: SQLGetPrivateProfileStringW

You may get the following:

isql: symbol lookup error: /u01/app/oracle/instantclient_11_2/libsqora.so.11.1: undefined symbol: SQLGetPrivateProfileStringW

This is normally because the driver manager (unixODBC) version is not compatible with the ODBC driver. Verify the #driver manager version needed for the oracle ODBC database version.




See below how to search for a symbol:

  • Symbol lookup
nm -A /u01/app/oracle/instantclient_11_2/libsqora.so.11.1 | grep SQLGetPrivateProfileStringW
/u01/app/oracle/instantclient_11_2/libsqora.so.11.1:                U SQLGetPrivateProfileStringW

SQLGetPrivateProfileStringW is defined in the last libodbc.so and libodbcinst.so.

nm -A /usr/lib/libodbcinst.so | grep SQLGetPrivateProfileStringW
Blank

5 - Documentation / Reference

db/oracle/odbc_linux.txt · Last modified: 2018/09/16 12:42 by gerardnico