0

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?

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
|improve this answer|||||
New contributor
Antonio N is a new contributor to this site. Take care in asking for clarification, commenting, and answering. Check out our Code of Conduct.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy