Oracle Database - ODBC Driver Insallation on Linux

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

Install driver manager: ODBC - Driver Manager

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

Add the library in the ld_library_path

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

Oracle Database - TNS_ADMIN environement variable

export TNS_ADMIN=/etc/oracle

dltest /u01/app/oracle/instantclient_11_2/libsqora.so.11.1

#SUCCESS: Loaded /usr/lib/oracle/11.2/client64/lib/libsqora.so.11.1

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

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

Example

• odbcinst.ini

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         =

• odbc.ini

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

with unixODBC - isql

isql -v OracleODBC-11g

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

• Installation Guide
• mkleehammer/pyodbc/wiki/Connecting-to-Oracle-from-RHEL-or-Centos
• http://stackoverflow.com/questions/13922415/how-do-i-setup-oracle-odbc-drivers-on-rhel-6-linux