cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
dramus
Responsive Resident
Responsive Resident

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.

 

  1. 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.
  2. You will need an account with the Oracle website to download the 64-Bit 19.3 ODAC installer 
  3. Unzip the file into a directory with a short name and no spaces in the path. The Oracle installer hates spaces and deep directories.
  4. 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.
  5. After the ODAC install completes, start an Administrator Command prompt window and navigate to the ODAC install directory.
  6. 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.

9 REPLIES 9
vnicholl
Frequent Visitor

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. 

bkan
Post Prodigy
Post Prodigy

@dramus Thanks ,

which setup.exe are you talking about?Can you please explain?

  1. 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
Responsive Resident
Responsive Resident

@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

dramus
Responsive Resident
Responsive Resident

In the top level of the folder that you unzipped the download to, there should be 2 folders and 2 files. The 2 files are setup.ini and setup.exe, you need to run the setup.exe file.

dramus_0-1626207557861.png

 

@dramus thanks and appreciate your patience, the only contents i see under the exracted files are in the snapshot. I do not see any setup.exe files.

Capture.PNG

dramus
Responsive Resident
Responsive Resident

That's the 12.2 install of ODAC, my post was for the 19.3 install.

Oh ok . Thanks a lot for your patience and time , will repost my ask.

Thanks

amitchandak
Super User
Super User

@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.

Did I answer your question? Mark my post as a solution! Appreciate your Kudos!! !!
Dashboard of My Blogs !! Connect on Linkedin

Learn Power BI Beginners !! Advance Power BI Concepts !! Power BI For Tableau User !! Learn Power BI in Hindi !!
Proud to be a Super User!

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

November Power BI Update 768x460.png

Check it Out!

Click here to read more about the November 2021 Updates!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.