Oracle Database connection issue

[sudhakar_kb]

Hi All,
I am trying to connect to oracle database from UI path activity.
I tested connection to oracle database using connection wizard, selected “Oracle.ManagedDataAccess” as provider in connection wizard.

Now on the activity DB connection properties, I used provider as “System.Data.Oracleclient” as I was not getting the “Oracle.ManagedDataAccess.client” in imports (as package was not installed)

Then I went ahead and tested the flow, I got following error:
{Source: Connect to database

Message: Attempt to load Oracle client libraries threw BadImageFormatException. This problem will occur when running in 64 bit mode with the 32 bit Oracle client components installed.

Exception Type: System.InvalidOperationException}

This issue is due to conflict of 32bit and 64 bit files.
I have installed oracle 19c 64 bit database on windows 8.1 (64 bit).
The UI path is 32 bit installed on 64 bit windows. I went through forums and it was suggested that I should install oracle 32 bit instant client. When I tried to do that it was giving some error due to oracle_home directory over-ridding.

Query: Should I select orcle_home has already installed oracle 64 bit home? Will the files get overwritten? Or should I install 32 bit client in different directory?
See the attached file for message that is coming.

Till this time, connection wizard on UI path activity worked fine.

Later I did some more research, and then found ODP.NET provider. So I installed package

For this I installed package “Oracle Data provider for .NET Managed Driver 21.5.0”
Here it says “ODP.NET, Managed Driver is a 100% native code .NET Framework driver for Oracle Database. No additional Oracle Client software is required to be installed to connect to Oracle Database.”
In the connection wizard I changed
After this I changed UIPath DB connection activity properties, provider name - Oracle.ManagedAccess.Client.
Now I got following error
{"Message: Could not load file or assembly ‘Oracle.ManagedDataAccess, Version=4.122.19.1, Culture=neutral, PublicKeyToken=89b483f429c47342’ or one of its dependencies. The system cannot find the file specified.

Exception Type: System.IO.FileNotFoundException"

}

After I did this changes to point to Oracle.ManagedDataAccess.Client, the connection wizard is not opening the box. I restarted the UIPath and still the same issue. I am using community edition of UIPath 2022.40.0 version.

I am aware that connection can also be made using Microsoft ODBC data source, but I want to try ODP.NET provider. Do we still need oracle 32 bit instant client with this option?

Once I uninstalled package “Oracle Data provider for .NET Managed Driver 21.5.0”, then connection wizard is working fine.

Can anyone help me with this issue?

Regards,
Sudhakar

[sudhakar_kb]

I managed to resolve the problem.
When I installed oracle 19c 64 bit database, the ODP.NET components were also installed - all the relevant dlls.
The registry entries were as given below.
HKLM\Software\Oracle\ODP.NET\2.122.19.1\DllPath – c:\oracle_sw\bin
HKLM\Software\Oracle\ODP.NET\4.122.19.1\DllPath – c:\oracle_sw\bin

HKLM\Software\Oracle\ODP.NET.Managed\4.122.19.1\TNS_ADMIN – C:\oracle_sw\network\admin

In C:\oracle_sw\bin folder there is no dll related to ODAC (i.e. oracle.*DataAccess.dll)
In windows environment, path was having C:\oracle_sw\bin.

Other than this there were no entries in registry, which can give a hint. So I searched for “Oracle.ManagedDataAccess.dll” file in my local machine. I found the file in folder
C:\oracle_sw\ODP.NET\managed\common

It also has 2 more folders (as given below) containing file

• Oracle.ManagedDataAccessIOP.dll
  C:\oracle_sw\ODP.NET\managed\x64
  C:\oracle_sw\ODP.NET\managed\x86

Also there were 2 folders (as given below) containing file - Oracle.DataAccess.dll
C:\oracle_sw\ODP.NET\bin\2.x
C:\oracle_sw\ODP.NET\bin\4
This is probably needed for unmanaged access.

Having found Oracle.ManagedDataAccess.dll file, I added, “C:\oracle_sw\ODP.NET\managed\common” folder in OS environment Path.
In UIPath DB connection activity, provider name is given as Oracle.ManagedDataAcces.client

Though I downloaded oracle client 64 bit version and also 32 bit version but I have not installed.
With Oracle database 19c 64 bit installed on my machine, ODP.NET came by default (containing relevant dlls for both 32 bit and 64 bit). Hence it worked. I was able to fetch data from database.

Now when one doesn’t have oracle database installed on their machine (with 64 bit OS) then one should download Oracle client 64 bit version and install the same on local machine from where data access is needed. I guess oracle will install relevant dlls for 32 bit and 64 bit.

After following steps given above, Oracle connection should be successful using ODP.NET on 64 bit machine using 32 bit UI Path.
One more point for ODP.NET to work Microsoft .NET framework 4 or higher should be installed on machine

Now DB connection wizard is also opening (as I didn’t install the package - “Oracle Data provider for .NET, managed driver” on UI path.

Hope this helps others also.

Finally the following statement lead me to do the research.
“ODP.NET, Managed Driver is a 100% native code .NET Framework driver for Oracle Database. No additional Oracle Client software is required to be installed to connect to Oracle Database.”

Regards,
Sudhakar