OLE DB Connection To Oracle Failing

[DH_UOW]

Hi,

I have found that connecting to an Oracle DB using the Oracle Connector in Power Bi is very slow (Tableau is up to 30 times faster on large extracts of the same data).

Someone has suggested that the OLE DB connector is faster.  However I am having troubles getting that to recognise the install.

I am getting the error "OraOLEDBpus19.dll: The specified module could not be found".

As can be seen the OLE DB driver has been registered.

The file exists in my ORACLE_HOME/bin folder

This folder exists in my path

>echo %path%
C:\Program Files\Oracle Client for Microsoft Tools\;C:\oracle\product\19.0.0\client_64bit\bin;

I have tried reinstalling the Oracle Client; ODAC, Oracle Client for Microsoft Tools; and have restarted about 5 times.

I can still connect using the standard Oracle connector, but the error persists with trying to use OLE DB.

Checking with a collegue they have the same error.  We have installed the Oracle 19 client with Administration options while running as Windows Administrator (two different applications of Administrator there, I haven't confused one with another).

Has anyone got anything that might help resolve this issue?

[DH_UOW · Accepted solution]

Thanks Gao,

I had already tried all of the above suggestions.

In the end I gave up with OLE DB and re-tackled the slow speed of the standard connector.

I found that the issue is the Fetchsize.  The Oracle client's default is too small for analytical type data extracts, but Power Bi doesn't overide this with a better value in its provided connector.

I ended up having to modify the registry (Computer\HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE\ODP.NET\4.122.19.1) and adding Fetchsize = 4194304 (trial for a suitable value).

https://docs.oracle.com/en/database/oracle/oracle-database/19/odpnt/InstallUnmanagedConfig.html#GUID...

The other solution would have been to modify Microsoft.Mashup.Container.NetFX45.exe.config, however for whatever reason, I can't modify anything in my Power Bi install on my machine using local admin (I can modify the registry however, go figure).  This would probably be preferable. The response from @Adamboer in the following post was useful input but without access to modify the file it wasn't going to help.

https://community.fabric.microsoft.com/t5/Power-Query/Performance-tuning-Oracle-ODAC-driver-with-Fet...

[Anonymous]

Hi @DH_UOW ,

Please check:

1. Register the OLE DB provider: Try registering the OLE DB provider manually using the command. Open a command prompt with administrator privileges and run the following command, like:

regsvr32 "C:\oracle\product\xx.0.0\client_64bit\bin\OraOLEDBpus19.dll"

Replace the path with the correct path to the file on your system.OraOLEDBpus19.dll

2. Check for conflicting Oracle installations: If you have multiple Oracle installations on your system, there might be conflicts between them. Uninstall any unnecessary Oracle installations and ensure that only the required version is installed.

3. Check the version of the OLE DB driver you are using. Make sure it is compatible with the version of the Oracle database you are trying to connect to.

Developer's Guide for Microsoft Windows (oracle.com)

4. Check to see if there are any conflicts with other software or drivers on your system that may be causing the issue. You can try disabling any antivirus or firewall software temporarily to see if that resolves the issue.

Best Regards,
Gao
Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly --  How to provide sample data in the Power BI Forum