Delphi ODAC and Oracle instant client. TNS_ADMIN issue and others.

Discussion of open issues, suggestions and bugs regarding ODAC (Oracle Data Access Components) for Delphi, C++Builder, Lazarus (and FPC)
Post Reply
Piotr Z
Posts: 1
Joined: Mon 01 Apr 2019 10:33

Delphi ODAC and Oracle instant client. TNS_ADMIN issue and others.

Post by Piotr Z » Mon 01 Apr 2019 14:18

Hello Support.

I would like to raise few topics related to logging to Oracle database with ODAC and Oracle Instant Client. We are having several problems with it. Details in three issues described below.

1. Instant client and no registry entries

Basic facts:
- Application: Delphi 10.2 /10.3, newest ODAC, all 32-bit
- Oracle Instant client for Oracle 12c/18c (32bit)
- Running environment: remote system accessible via Remote desktop (RDP). 64-bit Windows
- Instant client unzip location: c:\InstantClient
- Following env. variables are set:
- ORACLE_HOME=c:\InstantClient
- TNS_ADMIN=c:\InstantClient
- NLS_LANG=AMERICAN_AMERICA.WE8ISO8859P15
- PATH=c:\InstantClient;%PATH%
- There are no entries in Windows registry for that ORACLE_HOME in Computer\HKEY_LOCAL_MACHINE\SOFTWARE\WOW6432Node\Oracle.
- There are other entries for other Oracle clients installed (registry entries in Computer\HKEY_LOCAL_MACHINE\SOFTWARE\WOW6432Node\Oracle).

Problem:
It seems that the TConnectDialog.GetServerList function does not find the c:\InstantClient\tnsnames.ora. It returns empty list. We tested that also on ODACDEMO.exe application with the same behaviour. It does detect the Instant client ORACLE_HOME (named "Default [InstantClient1]") but the GetServerList returns empty list.

We have found a workaround by using following configuration:
- Instant client unzip location: c:\InstantClient\bin
- ORACLE_HOME=c:\InstantClient
- TNS_ADMIN=c:\InstantClient\network\admin
- TNS entries: c:\InstantClient\network\admin\tnsnames.ora

Another workaround is to create entries in registry.

From our perspective it looks like the TNS_ADMIN and probably also NLS_LANG environment variables are ignored. It seems that tnsnames.ora is only found by ODAC when it is in "default" location: %ORACLE_HOME%\network\admin.
Please note that other applications that use this instant client setups work without problems, they correctly detect values of TNS_ADMIN variable and show the list of server entries defined in tnsnames.ora regardless of the location.

Please help us configure this client. Unfortunately the ODAC documentation is very limited. We would like to avoid creating new registry entries for this setup and only focus on setting the environment variables only.


2. Problem with refreshing TConnectDialog.GetServerList after changing the associated OraSession.Home or OraSession.HomeName.

We would like to allow the user to dynamically change the ORACLE_HOME definition during the database login in runtime. By default, the default Oracle client configuration should be used but we would like to allow the user to change that. Unfortunately the ODAC documentation is also very limited. We would like to know how we can force the refresh of TConnectDialog.GetServerList after changing the associated OraSession.Home or OraSession.HomeName value.
It does seem to work in default ConnectDialog provided by ODAC (and shown in ODACDemo example) but we would like to use our own logic and our own login dialog (we don't have ODAC sources version).
How can we do that in the code?


3. "Cannot Load OCI"

Third issue is related to our customer. We have "Cannot Load OCI" exception when our application uses instant Oracle client and it is executed from RDP session as a user which is not a local admin. The system is 64bit Windows server.
The Instant client and the Delphi/ODAC application are 32bit. Please also note that the application uses Instant Oracle Client with ORACLE HOME entry defined in both registry and environment variables. Please note that application works without problems with other, non Instant, Oracle clients defined on that machine. Also, the application has been installed by different user (local admin) and it is being started by other, non-admin user.
The exception is "Cannot Load OCI: <here is the correct path to oci.dll>"

We are still investigating (potentially access topic) but we could use your comments about that issue.

Kind Regards
Piotr

MaximG
Devart Team
Posts: 1822
Joined: Mon 06 Jul 2015 11:34

Re: Delphi ODAC and Oracle instant client. TNS_ADMIN issue and others.

Post by MaximG » Tue 02 Apr 2019 15:05

1. Thank you for the information. We have reproduced the issue and will investigate its origin.
2. The source codes of the TConnectDialog component that is included in all editions of ODAC ([ODAC install folder]\Source). Thus, you can implement any behavior on its base.
3. We have checked the behavior of our product according to the description you provided and found no issues. Please specify whether you can establish a connection to the Server Oracle in the described case without using our product (e.g. with SQL*Plus or Oracle SQL Developer).

Post Reply