1

Folks,

We installed unixODBC-2.3.7 on Aix 7.1 to connect to Oracle 11g. After setup odbc.ini and odbcinst.ini files and tried to connect with isql, we had the following error:

# isql -v ORACLE
[IM002][unixODBC][Driver Manager]Data source name not found and no default driver specified
[ISQL]ERROR: Could not SQLConnect

Before execute the isql command, we ran the following export commands:

export ODBCSYSINI=/usr/local/etc
export PATH=$PATH:/usr/local/bin
export TNS_ADMIN=/u01/app/oracle/product/11.2.0.4/client_1/network/admin
export ODBCINI=/usr/local/etc/odbc.ini
export LIBPATH=/u01/app/oracle/product/11.2.0.4/client_1/lib:/usr/local/lib:/u01/app/oracle/product/11.2.0.4/client_1
export LD_LIBRARY_PATH=/u01/app/oracle/product/11.2.0.4/client_1/lib:/usr/local/lib:/u01/app/oracle/product/11.2.0.4/client_1

And odbcinst -j command too:

unixODBC 2.3.7
DRIVERS............: /usr/local/etc/odbcinst.ini
SYSTEM DATA SOURCES: /usr/local/etc/odbc.ini
FILE DATA SOURCES..: /usr/local/etc/ODBCDataSources
USER DATA SOURCES..: /usr/local/etc/odbc.ini
SQLULEN Size.......: 4
SQLLEN Size........: 4
SQLSETPOSIROW Size.: 2
Illegal instruction(coredump)

Our /usr/local/etc/odbc.ini file is:

[ODBC Data Sources]
ORACLE =  Oracle ODBC Test

[ORACLE]
Application Attributes = T
Attributes = W
BatchAutocommitMode = IfAllSuccessful
BindAsFLOAT = F
CloseCursor = F
DisableDPM = F
DisableMTS = T
Driver = Oracle11gdriver
DSN = ORACLE
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 = ORAODBC.customer.com
SQLGetData extensions = F
Translation DLL =
Translation Option = 0
DisableRULEHint = T
UserID = user
Password = pass
StatementCache=F
CacheBufferSize=20
UseOCIDescribeAny=F

And our /usr/local/etc/odbcinst.ini is

[Oracle11gdriver]
Description     = Oracle ODBC driver for Oracle 11g
Driver          = /u01/app/oracle/product/11.2.0.4/client_1/lib/libsqora.so

Finally, our tnsnames.ora is:

ORAODBC =
   (DESCRIPTION =
     (ADDRESS_LIST =
       (ADDRESS = (PROTOCOL = TCP)(HOST = customer.com.br)(PORT = 1521))
   )
   (CONNECT_DATA =
      (SERVICE_NAME = ORAODBC.customer.com)
   )
 )

Any idea what is going on?

1 Answer 1

0

I had similar issues in RHEL 5/7. To resolve it, I installed Oracle Instant Client, and used it, I think unixODBC is designed to be used with instant client, and not with full oracle client.

Oracle Instant Client for RHEL (you can use 11/12/18/19 versions):

sudo yum localinstall -y oracle-instantclient18.5-basic-18.5.0.0.0-3.x86_64.rpm
sudo yum localinstall -y oracle-instantclient18.5-sqlplus-18.5.0.0.0-3.x86_64.rpm
sudo yum localinstall -y oracle-instantclient18.5-odbc-18.5.0.0.0-3.x86_64.rpm

unixODBC from source:

curl -o unixODBC-2.3.7.tar.gz http://www.unixodbc.org/unixODBC-2.3.7.tar.gz
tar -zxvf unixODBC-2.3.7.tar.gz
cd unixODBC-2.3.7
./configure --prefix=/ --sysconfdir=/etc
make
sudo make install
#/etc/odbcinst.ini
[Oracle]
Description     = ODBC for ORACLE (64 bit)
Driver          = /usr/lib/oracle/18.5/client64/lib/libsqora.so.18.1
Setup           =
FileUsage       = 1
CPTimeout       =
CPReuse         =
Driver Logging  = 7
#/etc/odbc.ini
[mydnsname]
Application Attributes = T
Attributes = W
BatchAutocommitMode = IfAllSuccessful
BindAsFLOAT = F
CloseCursor = F
DisableDPM = F
DisableMTS = T
Driver = Oracle   #the name from [Oracle] in /etc/odbcinst.ini
DSN = mydnsname
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 = myservicename    #servicename from tnsnames.ora
SQLGetData extensions = F
Translation DLL =
Translation Option = 0
DisableRULEHint = T
UserID = schemaname
Password = schemapass
StatementCache=F
CacheBufferSize=20
UseOCIDescribeAny=F
MaxTokenSize=8192
#tnsnames.ora
myservicename =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = oraclevip.domain.com)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = myservicename)
    )
  )
# ~/.bashrc
export ORACLE_HOME=/usr/lib/oracle/18.5/client64
export LD_LIBRARY_PATH=/usr/lib:/usr/lib/oracle/18.5/client64/lib
export TNS_ADMIN=$ORACLE_HOME/network/admin #optional

Check your environment variables, then check the dependencies:

NOTE: for some distros you need this

sudo ln -s libodbcinst.so.2 libodbcinst.so.1

Check this (it must resolve all files required) you can adjust LD_LIBRARY_PATH:

$ldd /usr/lib/oracle/18.5/client64/lib/libsqora.so.18.1
        linux-vdso.so.1 =>  (0x00007ffd417fd000)
        libdl.so.2 => /lib64/libdl.so.2 (0x00007fe43a762000)
        libm.so.6 => /lib64/libm.so.6 (0x00007fe43a460000)
        libpthread.so.0 => /lib64/libpthread.so.0 (0x00007fe43a244000)
        libnsl.so.1 => /lib64/libnsl.so.1 (0x00007fe43a02a000)
        librt.so.1 => /lib64/librt.so.1 (0x00007fe439e22000)
        libaio.so.1 => /lib64/libaio.so.1 (0x00007fe439c20000)
        libresolv.so.2 => /lib64/libresolv.so.2 (0x00007fe439a07000)
        libclntsh.so.18.1 => /usr/lib/oracle/18.5/client64/lib/libclntsh.so.18.1 (0x00007fe435a49000)
        libclntshcore.so.18.1 => /usr/lib/oracle/18.5/client64/lib/libclntshcore.so.18.1 (0x00007fe435466000)
        libodbcinst.so.2 => /lib/libodbcinst.so.2 (0x00007fe43524b000)
        libc.so.6 => /lib64/libc.so.6 (0x00007fe434e7e000)
        libgcc_s.so.1 => /lib64/libgcc_s.so.1 (0x00007fe434c68000)
        /lib64/ld-linux-x86-64.so.2 (0x00007fe43ac2a000)
        libmql1.so => /usr/lib/oracle/18.5/client64/lib/libmql1.so (0x00007fe434a08000)
        libipc1.so => /usr/lib/oracle/18.5/client64/lib/libipc1.so (0x00007fe434596000)
        libnnz18.so => /usr/lib/oracle/18.5/client64/lib/libnnz18.so (0x00007fe433e41000)
        libons.so => /usr/lib/oracle/18.5/client64/lib/libons.so (0x00007fe433bf1000)

That's all.

To test, you can use isql

$isql -v mydnsname
+---------------------------------------+
| Connected!                            |
|                                       |
| sql-statement                         |
| help [tablename]                      |
| quit                                  |
|                                       |
+---------------------------------------+
SQL> select * from cat;
+-------------------------------+------------+
| TABLE_NAME                    | TABLE_TYPE |
+-------------------------------+------------+
| TEST_TABLE                    | TABLE      |
+-------------------------------+------------+
SQLRowCount returns -1
1 rows fetched

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.