Power BI Desktop needs the Oracle data providers installed at the machine level. After ODAC 12.2, Oracle removed this option from the install, which means out of the box Oracle 18.x and 19.x ODAC installs are not seen by Power BI Desktop. I've spent the last 24 hours installing and uninstalling various versions of ODAC trying to get them to work with Power BI Desktop.I'm limiting this to the 64bit install (Because that is what I have) and .NET 4.0, becuase that is what Power BI Desktop needs.I can now report I have ODAC 19.3 working with Power BI Desktop (May 2021). This is thanks to Google and a lot of digging around. I've put a link to the original article I found that helped me get the data providers installed, as this was not my own work! (Power BI Github comment ), I'm just documenting it here for others. You need to match the 32/64 bit version of the Oracle ODAC install to the version of Power BI Desktop you have installed. You can find this by starting up Power BI Desktop and clicking Help->About.You will need an account with the Oracle website to download the 64-Bit 19.3 ODAC installer Unzip the file into a directory with a short name and no spaces in the path. The Oracle installer hates spaces and deep directories.Run the Setup.exe with Administrator privileges. Other than selecting the oracle base directory and possibly changing the path for the install, you shouldn't need to alter any other settings. If youdo not have a thsnames file, it will prompt you to create a first entry for one.After the ODAC install completes, start an Administrator Command prompt window and navigate to the ODAC install directory.Navigate to odp.net\bin\4 NOTE: Look in c:\windows\microsoft.net\framework64 to see the correct versions for .net there should be a v4.0.xxxx folder use these strings as frameworkversion below. In my case it was 4.0.30319.You will need to run: OraProvCfg.exe /action:gac /providerpath:"Oracle.DataAccess.dll" OraProvCfg /action:config /force /product:odp /component:dbproviderfactory /frameworkversion:v4.0.30319 /providerpath:Oracle.DataAccess.dll 7. One last thing, from the main install directory take a copy of the oraons.dll and copy it into the bin directory. Having done the above I was able to start Power BI Desktop and connect to an Oracle data source.

After all the hours spent trying to solve this issue it was my companies rolled out version of PBI Desktop. It was right in the article I had read 100 times but skimmed over because I have PBI desktop pushed to my machine. I had to have IT remote in to install. If you downloaded Power BI Desktop from the Microsoft Store, you might be unable to connect to Oracle databases because of an Oracle driver issue. If you encounter this issue, the error message returned is: Object reference not set. This is an issue with how Oracle driver works in Windows UWP Apps. To address the issue, you have to do the following:Download Power BI Desktop from the Download Center instead of Microsoft Store.

I had similar issue after migrating on-premise gateway to a new server, I followed below steps. Installed Oracle Client for Microsoft Tools https://download.oracle.com/otn_software/odac/Oracle-Client-for-Microsoft-Tools.exe and copied the tnsnames.ora file which solved my issues.

dramus , Thanks for sharing. Oracle connection is a bit difficult. I also faced a lot of issues when I connected it for the first time.

dramus Thanks ,which setup.exe are you talking about?Can you please explain?Run the Setup.exe with Administrator privileges. Other than selecting the oracle base directory and possibly changing the path for the install, you shouldn't need to alter any other settings. If youdo not have a thsnames file, it will prompt you to create a first entry for one.Thanks

The ODAC installer. https://www.oracle.com/database/technologies/dotnet-odacdeploy-downloads.html

dramus

Continued Contributor

5 years ago

Getting Power BI Desktop to work with Oracle ODAC AFTER version 12.2

Power BI Desktop needs the Oracle data providers installed at the machine level. After ODAC 12.2, Oracle removed this option from the install, which means out of the box Oracle 18.x and 19.x ODAC installs are not seen by Power BI Desktop. I've spent the last 24 hours installing and uninstalling various versions of ODAC trying to get them to work with Power BI Desktop.

I'm limiting this to the 64bit install (Because that is what I have) and .NET 4.0, becuase that is what Power BI Desktop needs.

I can now report I have ODAC 19.3 working with Power BI Desktop (May 2021). This is thanks to Google and a lot of digging around. I've put a link to the original article I found that helped me get the data providers installed, as this was not my own work! (Power BI Github comment ), I'm just documenting it here for others.

You need to match the 32/64 bit version of the Oracle ODAC install to the version of Power BI Desktop you have installed. You can find this by starting up Power BI Desktop and clicking Help->About.
You will need an account with the Oracle website to download the 64-Bit 19.3 ODAC installer
Unzip the file into a directory with a short name and no spaces in the path. The Oracle installer hates spaces and deep directories.
Run the Setup.exe with Administrator privileges. Other than selecting the oracle base directory and possibly changing the path for the install, you shouldn't need to alter any other settings. If youdo not have a thsnames file, it will prompt you to create a first entry for one.
After the ODAC install completes, start an Administrator Command prompt window and navigate to the ODAC install directory.
Navigate to odp.net\bin\4
NOTE: Look in c:\windows\microsoft.net\framework64 to see the correct versions for .net there should be a v4.0.xxxx folder use these strings as frameworkversion below. In my case it was 4.0.30319.
You will need to run:

OraProvCfg.exe /action:gac /providerpath:"Oracle.DataAccess.dll"

OraProvCfg /action:config /force /product:odp /component:dbproviderfactory /frameworkversion:v4.0.30319 /providerpath:Oracle.DataAccess.dll

7. One last thing, from the main install directory take a copy of the oraons.dll and copy it into the bin directory.

Having done the above I was able to start Power BI Desktop and connect to an Oracle data source.

Tips and Tricks

23 Replies

uwaiz
New Member
3 years ago
I had similar issue after migrating on-premise gateway to a new server, I followed below steps.
Installed Oracle Client for Microsoft Tools https://download.oracle.com/otn_software/odac/Oracle-Client-for-Microsoft-Tools.exe and copied the tnsnames.ora file which solved my issues.
- tlzro
  New Member
  3 years ago
  Did you have to run these as well?
  
  OraProvCfg.exe /action:gac /providerpath:"Oracle.DataAccess.dll" OraProvCfg /action:config /force /product:odp /component:dbproviderfactory /frameworkversion:v4.0.30319 /providerpath:Oracle.DataAccess.dll
  - vnicholl
    Helper II
    3 years ago
    I'm not sure.
amitchandak
Super User
5 years ago
dramus , Thanks for sharing. Oracle connection is a bit difficult. I also faced a lot of issues when I connected it for the first time.
Anonymous
Not applicable
5 years ago
dramus Thanks ,
which setup.exe are you talking about?Can you please explain?
Run the Setup.exe with Administrator privileges. Other than selecting the oracle base directory and possibly changing the path for the install, you shouldn't need to alter any other settings. If youdo not have a thsnames file, it will prompt you to create a first entry for one.
Thanks
- dramus
  Continued Contributor
  5 years ago
  The ODAC installer. https://www.oracle.com/database/technologies/dotnet-odacdeploy-downloads.html
  - Anonymous
    Not applicable
    5 years ago
    dramus Thanks. I have already downloaded. Please see the below screen shot. I am confused about your STEP 4 which setup.exe we need to RUN under which folder?
    Thanks
vnicholl
Helper II
4 years ago
I am trying this on a laptop w/only Oracle 19c as the client. I did all this, still getting Oracle: ORA-12170: TNS:Connect timeout occurred.

Could this be due to ACL's not on this computer?

My TOAD works though - but it is not using the tnsnames.ora on the laptop.
- mkcongrove1
  Advocate I
  3 years ago
  Were you ever able to solve this issue? Could you share your solution if so? I have been stuck here for months now 😞
  - vnicholl
    Helper II
    3 years ago
    I got a brand new laptop with a brand new install of Oracle desktop client.

AnamPatel

Resolver II

4 years ago

Can you please let me know the steps to uninstall an older version of ODAC?

dramus

Continued Contributor

3 years ago

To uninstall a version of ODAC. I haven't run this, but it should be close.

OraProvCfg.exe /action:ungac /providerpath:"Oracle.DataAccess.dll"

OraProvCfg /action:unconfig  /product:odp /component:dbproviderfactory /frameworkversion:v4.0.30319

IncrementalValu

New Member

2 years ago

I made a slight change but overall it works well, thank you!

set oracle19cprovider="C:\oracle\ora19c64\client\ODP.NET\bin\4"
OraProvCfg.exe /action:ungac /providerpath:'%oracle19cprovider%\Oracle.DataAccess.dll'
OraProvCfg /action:unconfig /product:odp /component:dbproviderfactory /frameworkversion:v4.0.30319 /providerpath:'%oracle19cprovider%\Oracle.DataAccess.dll'

*Note, your location of Oracle.DataAccess.dll may be different than what I am using.

dramirezkon
Frequent Visitor
3 years ago
Hi, I'm trying to config but when I run
OraProvCfg /action:config /force /product:odp /component:dbproviderfactory /frameworkversion:v4.0.30319 /providerpath:Oracle.DataAccess.dll
showme this error:
C:\oracle\odp.net\bin\4>OraProvCfg /action:config /force /product:odp /component:dbproviderfactory /frameworkversion:v4.0.30319 /providerpath:Oracle.DataAccess.dll
ERROR: File not found: oracle.dataaccess.dll?
Error: Referencia a objeto no establecida como instancia de un objeto.

Can you help me?
- NathanMarston
  New Member
  3 years ago
  I ran into the same error - what i found I had to do was put quotes around "Oracle.DataAccess.dll", exactly like he did with the first call to OraProvCfg.
  - IncrementalValu
    New Member
    2 years ago
    I was still geting the error, even when utilizing quotes. i.e.
    
    OraProvCfg /action:config /force /product:odp /component:dbproviderfactory /frameworkversion:v4.0.30319 /providerpath:"Oracle.DataAccess.dll"
    
    I instead used a variable to define the current path of Oracle.DataAccess.dll and then used that which seemed to work:
    
    set oracle19cprovider="C:\oracle\ora19c64\client\ODP.NET\bin\4" OraProvCfg.exe /action:gac /providerpath:'%oracle19cprovider%\Oracle.DataAccess.dll' OraProvCfg /action:config /force /product:odp /component:dbproviderfactory /frameworkversion:v4.0.30319 /providerpath:'%oracle19cprovider%\Oracle.DataAccess.dll'
    
    *Note, your location of Oracle.DataAccess.dll may be different than what I am using.
duardo08
Advocate I
3 years ago
Thanks bro!
I was two days trying to put a dashboard to work in a vm

Forum Discussion

Getting Power BI Desktop to work with Oracle ODAC AFTER version 12.2

23 Replies

I had similar issue after migrating on-premise gateway to a new server, I followed below steps.

Installed Oracle Client for Microsoft Tools https://download.oracle.com/otn_software/odac/Oracle-Client-for-Microsoft-Tools.exe and copied the tnsnames.ora file which solved my issues.

Related content

Desktop old version

Licence for commercial use - Desktop Version

Version control

Power BI Desktop Version Issue

Need PowerBI Desktop 32-Bit Version 2.145.1602 (Last Version Update for 32-Bit)