Power BI Oracle DirectQuery fails, but Import works – is it because I need to install Oracle client?

[Zhinee]

I’m trying to connect Power BI Desktop to an Oracle database using the same credentials and connecting to the same DB.

Initially, Import mode also failed, but after checking the Enabled ODP Provider setting, I was able to connect in Import mode. 

However, when I switch to DirectQuery I get this error:
A connection could not be made to the data source with the Name of '{"protocol":"oracle","address":{"server":"x-db.xxxx.com:1234/xxxx"},"authentication":null,"query":null}'.

What could be the issue here?

[v-pgoloju]

Hi @Zhinee,

Thank you for reaching out to the Microsoft Fabric Forum Community, and special thanks to @Ritaf1983 and @tayloramy  for prompt and helpful responses.

Just following up to see if the Response provided by community members were helpful in addressing the issue.

If one of the responses helped resolve your query, please consider marking it as the Accepted Solution. Feel free to reach out if you need any further clarification or assistance.

Best regards,
Prasanna Kumar

[Ritaf1983 · Accepted solution]

Hi @Zhinee 

Import mode works because Power BI can use the bundled ODP provider when you enable that option.
DirectQuery does not support the bundled provider. It requires a locally installed Oracle client / ODP.NET (64-bit, matching your Power BI Desktop).

To fix the issue, install the Oracle Data Access Components (ODAC) or Oracle Instant Client with ODP.NET, and make sure your PATH environment variable points to it.
You can download the Oracle client here:
Oracle Instant Client Downloads: https://www.oracle.com/database/technologies/instant-client.html

Oracle Data Access Components (ODAC): https://www.oracle.com/database/technologies/dotnet-odacdeploy-downloads.html

If you still face issues after installation, I recommend opening a support ticket with Microsoft for further troubleshooting.

If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly

[Zhinee]

installing OCMT resolved the issue.
https://www.oracle.com/database/technologies/appdev/ocmt.html
Thank you very much!

[tayloramy]

Hi @Zhinee, 

Are you loading this through a data gateway? If so make sure that the Oracle Client for Microsoft Tools (OCMT) is installed on the gateway machine. 

If you found this helpful, consider giving some Kudos. If I answered your question or solved your problem, mark this post as the solution.