Connection Oracle on Mac OSX

[martinvb]

Dear sir, madam,

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)))"

Though, I can't get it working any other manner. The error I receive from

Code: Select all

isql -v OURSID

is:

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

My situation is as follows:
- 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

- odbcinst -j yields

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 odbcinst.ini file is straightforward (Driver /usr/local/devart/odbvoracle/lib/libdevartodbvoracle.x64.dylib etc), but I don't expect an error here since sqlplus connects.
- 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)

My colleagues and I have spend hours studying the tnsnames.ora and sqlnet.ora, which are the same files are used on the Windows machines. Their info is contained in the sqlplus line above, but to keep this message a bit brief I won't copy the information.

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

[AlexP]

Try to leave only the real name OURSID in the SID parameter without additional parameters.

[martinvb]

Thanks for the suggestion. I tried

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

and

Code: Select all

[OURSID]
Driver=Devart ODBC Driver for Oracle x64
SID=OURSID

Both did not resolve the problem, same error:

[08001][unixODBC][Devart][ODBC][Oracle]ORA-12545: Connect failed because target host or object does not exist
[ISQL]ERROR: Could not SQLConnect

Are you sure the problem is in the odbc.ini and not in the .ora files?

[AlexP]

We can't reproduce the issue. Try to use the ServiceName parameter instead of SID

Code: Select all

ServiceName=OURSID

If the issue repeats, please open access to your server and we will try to reproduce the issue on our side.

P.S. Direct Mode doesn't use Oracle client, as well as information from the tnsnames.ora file

[martinvb]

Sorry, that didn't work. I am not able to give direct access to this network. Are you located in (or near) the Netherlands?

Still, it says the problem lies with connecting to the host, so it looks like something goes wrong with the preliminaries... Could you tell if the problem lays in the tnsnames.ora or the driver settings odbc.ini?

If I get a direction over there, it may help to debug. Also see the first post in this thread with the path settings.

Kind regards, and thanks again,

Mart

[AlexP]

Try executing the OURSID command. Let us know the results.