Connection Oracle on Mac OSX

Connection Oracle on Mac OSX

Postby martinvb » Wed 22 Jun 2016 15:49

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
martinvb
 
Posts: 3
Joined: Wed 22 Jun 2016 15:12

Re: Connection Oracle on Mac OSX

Postby AlexP » Fri 24 Jun 2016 13:33

Try to leave only the real name OURSID in the SID parameter without additional parameters.
AlexP
Devart Team
 
Posts: 5528
Joined: Tue 10 Aug 2010 11:35

Re: Connection Oracle on Mac OSX

Postby martinvb » Sat 25 Jun 2016 14:21

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?
martinvb
 
Posts: 3
Joined: Wed 22 Jun 2016 15:12

Re: Connection Oracle on Mac OSX

Postby AlexP » Mon 27 Jun 2016 09:17

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
AlexP
Devart Team
 
Posts: 5528
Joined: Tue 10 Aug 2010 11:35

Re: Connection Oracle on Mac OSX

Postby martinvb » Tue 28 Jun 2016 14:36

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
martinvb
 
Posts: 3
Joined: Wed 22 Jun 2016 15:12

Re: Connection Oracle on Mac OSX

Postby AlexP » Fri 01 Jul 2016 06:43

Try executing the OURSID command. Let us know the results.
AlexP
Devart Team
 
Posts: 5528
Joined: Tue 10 Aug 2010 11:35


Return to ODBC Drivers