Powerbi-docs: oracle version

[dkozhukhar]

Is it essential/better to use 12.1.0.2.4 ? Or I can go ok with any newver driver?

---

Document Details

⚠ Do not edit this section. It is required for docs.microsoft.com ➟ GitHub issue linking.

• ID: 382f269e-2072-ec03-7c88-335cbd5df919
• Version Independent ID: 46a896f9-4707-adc3-72cd-5444a5d0f802
• Content: Connect to an Oracle database - Power BI
• Content Source: powerbi-docs/desktop-connect-oracle-database.md
• Service: powerbi
• Sub-service: powerbi-desktop
• GitHub Login: @davidiseminger
• Microsoft Alias: davidi

[maggiesMSFT]

Hi, @dkozhukhar -- thanks for your question! I've assigned this to the author. In the meantime, I suggest asking the Power BI community, https://community.powerbi.com/.

[sloggiz]

Hey, I also wonder about Oracle version. Currently I'm unable to use DirectQuery in PBI Desktop (ResportServer vJan2019) when connecting to Oracle DB. My Oracle client version is 12.2.0.1.0 (more recent than the recommended one)

[markymark1985]

To get 12.2.0.1.0 to work, you should be able to first install 12.1.0.2.4, select machine-wide configuration during setup, then upgrade to 12.2 after. starting in 12.2, Oracle removed the Machine-wide configuration which is what Power BI desktop and PBIRS depend on to see that Oracle client is installed.
https://docs.oracle.com/cd/E85694_01/ODPNT/InstallConfig.htm#ODPNT-GUID-08AB93CF-3E3F-4CA6-9D46-588844E40345
Starting with release 12.2, ODAC installs default to non-machine-wide configuration for a new Oracle home installation. For existing Oracle homes, ODAC re-installs the default to the same configuration setting chosen for that Oracle home from the previous installation. In non-ODAC installations, such as the standard Oracle Database or Client installation, only non-machine-wide installation is available.

If you want to go to a higher version, such as 18.3. get it here https://www.oracle.com/technetwork/topics/dotnet/downloads/odacdeploy-4242173.html

Then you need to do a manual "Machine-wide" configuration because of what is documented here: https://www.oracle.com/technetwork/topics/dotnet/downloads/install183-5191071.html

Machine-Wide Configuration

Machine-wide configuration is no longer supported beginning with ODAC 18c. Administrators can still place ODP.NET in the GAC and add the configuration section handler and DbProviderFactory information to machine.config manually if they wish to override ODP.NET settings for individual applications.

Manually install 18.3 so it can be recognized by 64 bit PBI desktop and PBIRS with these commands (changing paths to match the path of your oracle client installation

Power BI Reportserver and Report Builder Commands(they use Managed ODP.net)
Register ODP.net Managed Client to GAC
C:appclientuserproduct18.0.0client_1odp.netbin4OraProvCfg.exe /action:gac /providerpath:C:appclientuserproduct18.0.0client_1odp.netmanagedcommonOracle.ManagedDataAccess.dll

Add ODP.net Managed Client entries to machine.config
C:appclientuserproduct18.0.0client_1odp.netbin4OraProvCfg.exe /action:config /force /product:odpm /frameworkversion:v4.0.30319 /productversion:4.122.18.3

Add ODP.net Managed Client x86 entries to machine.config. Made my SSRS Report Builder start to work as its 32 bit
C:appclientuserproduct18.0.0client_1odp.netmanagedx86OraProvCfg.exe /action:config /force /product:odpm /frameworkversion:v4.0.30319 /productversion:4.122.18.3

Power BI Desktop Uses Unmanaged ODP.net:

Register ODP.net Unmanaged Client to GAC
C:appclientuserproduct18.0.0client_1odp.netbin4OraProvCfg.exe /action:gac /providerpath:C:appclientuserproduct18.0.0client_1odp.netbin4Oracle.DataAccess.dll

Add ODP.net Unmanaged Client entries to machine.config
C:appclientuserproduct18.0.0client_1odp.netbin4OraProvCfg.exe /action:config /force /product:odp /frameworkversion:v4.0.30319 /productversion:4.122.18.3

[dkozhukhar]

Thank you for a very elaborate answer!

[emorgoch]

Thanks @markymark1985, that solved my issues with Power BI Desktop 64-bit.
You wouldn't happen to have the commands to use for Power BI Desktop / Excel 32-bit as well would you? I have both the 32 and 64-bit ODAC components installed, and I can get the data using both 32 and 64-bit apps. Just trying to resolve this last dialog about the driver not being in stalled when using a PowerQuery app under 32-bit.

[markymark1985]

Thanks @markymark1985, that solved my issues with Power BI Desktop 64-bit.
You wouldn't happen to have the commands to use for Power BI Desktop / Excel 32-bit as well would you? I have both the 32 and 64-bit ODAC components installed, and I can get the data using both 32 and 64-bit apps. Just trying to resolve this last dialog about the driver not being in stalled when using a PowerQuery app under 32-bit.

The 32 bit driver can be found here https://www.oracle.com/technetwork/topics/dotnet/downloads/odacdev-4242174.html and after installation the following commands need to be executed:

Assuming files were installed to c:oracle32

For Report Builder v3 which uses Managed ODP.net:
Register ODP.net Managed Client to GAC
C:oracle32product18.0.0client_1odp.netbin4OraProvCfg.exe /action:gac /providerpath:C:oracle32product18.0.0client_1odp.netmanagedcommonOracle.ManagedDataAccess.dll

Add ODP.net Managed Client entries to machine.config
C:oracle32product18.0.0client_1odp.netbin4OraProvCfg.exe /action:config /force /product:odpm /frameworkversion:v4.0.30319 /productversion:4.122.18.3

Power BI Desktop 32 bit which uses Unmanaged ODP.net:

Register ODP.net Unmanaged Client to GAC
C:oracle32product18.0.0client_1odp.netbin4OraProvCfg.exe /action:gac /providerpath:C:oracle32product18.0.0client_1odp.netbin4Oracle.DataAccess.dll

Add ODP.net Unmanaged Client entries to machine.config
C:oracle32product18.0.0client_1odp.netbin4OraProvCfg.exe /action:config /force /product:odp /frameworkversion:v4.0.30319 /productversion:4.122.18.3

[dkozhukhar]

The 32 bit driver can be found here (option ODAC OUI)

looks like there should be a link and its gone )

[markymark1985]

Updated. https://www.oracle.com/technetwork/topics/dotnet/downloads/odacdev-4242174.html

[emorgoch]

Perfect. Worked and I have both 64 and 32-bit connections going at once.

[davidiseminger]

Great conversation! Thanks for providing the learning and support in this thread.

[tripleacoder]

What are the requirements for Power BI Gateway?
I get the error even after manually registering Oracle.ManagedDataAccess.dll in the GAC.