I'm having problems trying the ODBC connection working for one of our customers, and have spend quite a few days trying to resolve it. One of the problems on MacOSX was that the previous version of oracle instant client had a bug that didn't allow capitalized passwords, that was resolved last Friday. So if somebody encounters this post due to an username/password combination that is not accepted, look into this.
We are able to -correctly- establishing a connection with the following direct sqlplus command:
Code: Select all
sqlplus "myusername/pAsSw0rd@(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(Host=xxx.xxx.xxx.xxx)(Port=1521))(CONNECT_DATA=(SID=OURSID)(SERVICE_NAME=OURSID)(SERVER=DEDICATED)))"
Code: Select all
isql -v OURSID
Code: Select all
[08001`][unixODBC][Devart][ODBC][Oracle]ORA-12545: Connect failed because target host or object does not exist
[ISQL]ERROR: Could not SQLConnect
- I have the most recent version of the oracle instant client 12 installed, version of today.
- tnsnames.ora and sqlnet.ora are located in /Library/Oracle/instantclient_12_1/network/admin/tnsnames.ora and /Library/Oracle/instantclient_12_1/network/admin/sqlnet.ora
- The ~/.bash_profile file looks as follows:
Code: Select all
PATH=$PATH:/Library/Oracle/instantclient_12_1:/opt/local/bin
DYLD_LIBRARY_PATH=/Library/Oracle/instantclient_12_1
TNS_ADMIN=/Library/Oracle/instantclient_12_1
ORACLE_HOME=/Library/Oracle/instantclient_12_1
SQLPATH=/Library/Oracle/instantclient_12_1
ORACLE_SID=OURSID
export PATH
export DYLD_LIBRARY_PATH
export TNS_ADMIN
export SQLPATH
export ORACLE_HOME
export ORACLE_SID
Code: Select all
unixODBC 2.3.2
DRIVERS.........: /opt/local/etc/odbcinst.ini
SYSTEM DATA SOURCES: /opt/local/etc/odbc.ini
FILE DATA Sources..: /opt/local/etc/ODBCDataSources (this is an empty dir)
USER DATA SOURCES: /Users/administrator/.odbc.ini
SQLULEN Size : 8
SQLLEN Size : 8
SQLSETPOSIROS Size : 8
- The odbc.ini file is as follows:
Code: Select all
[OURSID]
Driver=Devart ODBC Driver for Oracle x64
Direct=True
User ID=myusername
Password=pAsSw0rd
Host=xxx.xxx.xxx.xxx (without /oracle or anything else)
Port=1521
SID=OURSID
CONNECT_DATA=(SERVICE_NAME=OURSID)(SID=OURSID)(SERVER=DEDICATED)
The oracle server is not located over here, I can't do much at the host. We are not using a local host, and call this host by IP address only.
My questions are as follows:
1) Does the Devart ODBC for Oracle use the tnsnames.ora and sqlnet.ora files? Or does it only look at the odbc.ini file?
2) Is there a possibility to see how it tries to connect? Both isql -v OURSID and sqlplus OURSID does not show much debug information; it might help to see what address it takes. The current verbose output is quite minimalistic for debugging.
3) Knowing these settings, can you see why "isql -v OURSID" can not connect?
In the case I get it working within the next few days, you've got a new paying customer; I've got to make some similar connections with some of my other customers.
Please let me know if you require any additional information.
Many thanks in advance.
Kind regards,
~Martin