DirectQuery of Oracle DB Stuck Detecting Table Schema

[nfietz]

Hi everyone - below is an overview of my issue I'm hoping someone can help me with. If there's anything additional I can provide to help troubleshoot, please let me know.

ISSUE: When I try to connect to Power BI Desktop to an Oracle DB via DirectQuery, it sits in the "Detecting Table Schema" and never gets past that. Importing the data works fine, as does connecting via ODBC. Trying to figure out what's causing it to get stuck in this stage.

BACKGROUND: Originally I had gotten the error message shown below about 'The managed provider Oracle.DataAccess.Client could not be instantiated.' I tried installing .NET 5.0 and got the same error. I then registered unmanaged ODP.NET using the commands provided here. Once I did that, Power BI desktop got stuck in the "Detecting Table Schema." I tried to undo the unmanaged ODP.NET by using the steps for managed ODP.NET, but it's still stuck "Detecting Table Schema."

SOFTWARE:

• Power BI Desktop v. 2.106.883.0 (64 bit)
• Oracle Client 19.3 (64 bit)
• .NET 5.0
• ODAC 12.2c

[nfietz · Accepted solution]

Hi All,

For those interested, I had different Oracle clients installed on my machine so that was the starting point. We performed the steps below to resolve:

1. Uninstall all clients from machine
2. Install Oracle Database Client 19c from this Oracle site (scroll down to the database client) 
   1. Base Path: C:\app\oracle\product
   2. Software Location: C:\app\oracle\product\19.3\client_2
3. Add tnsnames.ora file to C:\app\oracle\product\19.3\client_2\network\admin
4. Make sure environment variables are correct
   1. ORACLE_HOME = C:\app\oracle\product\19.3\client_2
   2. TNS_ADMIN = C:\app\oracle\product\19.3\client_2\network\admin
   3. Delete old paths from the 'Path' variable from the old clients
      1. Should be a C:\app\oracle\product\19.3\client_2\bin path here
5. Install ODAC 19c from this Oracle site 
   1. We had to install this via Windows PowerShell
   2. We created a 19.1 folder so we had the resulting path C:\app\oracle\product\19.1
      1. We may have also created the odac_client folder in the 19.1 folder
   3. Below is the command syntax we used. The first C:\ location was where the ODAC download was.

I definitely wouldn't have been able to do this without help from one of our Oracle database admins, so recommend enlisting the help of IT if someone else runs into this issue.

[v-yiruan-msft]

Hi @nfietz ,

The following ones are the supported Oracle versions when connect to an Oracle database with Power BI Desktop:

• Oracle Server 9 and later
• Oracle Data Access Client (ODAC) software 11.2 or greater

Here are some threads which have the same problem as yours, please review their solutions. Hope they can also help you resolve the problem.

'Oracle.Databases.Client' could not be instantiated

---

In my haste (and ignorance)  I didn't install the full Oracle client 64 bit - only the ODAC components.

So, after deinstalling all Oracle clients I did the following which resolved the issues:

1. Install Oracle 12c 64bit Client into a specific directory ie. "c:\oracle64" 

Oracle Database Client (12.1.0.2.0) for Microsoft Windows (x64)

2. Install Oracle 12c Oracle Data Access Components 64 bit into the same directory (c:\oracle64).

64-bit ODAC 12.2c Release 1 (12.2.0.1.0) for Windows x64

3.  Install Oracle 12c 32bit Client into another specific directory ie. "c:\oracle32"

Oracle Database Client (12.1.0.2.0) for Microsoft Windows (32-bit)

The results are good for my situation.  I am now able to connect to an Oracle database from PowerBI desktop 64bit without issue.  Additionally I can build visualizations over the connection.  Finally my Visual Studio projects which use oracle connections also function as expected - using the 32bit Oracle client.

---

Check if server password is not expired. That was the problem in our case.

---

Retrieving Data Problem With Direct Query From Oracle Database

---

 in my case when ı installed 32 bit oracle version 11.2.0 and 32 bit power bi to virtual machine (not my local machine) and the problem has gone.

---

Best Regards

[nfietz]

@Ying Thank you for pointing me to these threads. I'm having issues with the Oracle install/uninstall (screen appears and then immediately disappears) so am waiting on our IT folks to help. I'll respond back with my results once they're able to dig into this.

[v-yiruan-msft]

Hi @nfietz ,

Thanks for your reply. If you have any progress and problem which need us to help, please feel free to let us know. Thank you.

Best Regards

[Syndicate_Admin]

I think it will be very useful to others as well.

[amitchandak]

@nfietz , I think the oracle client is not properly installed. You need to install that. Add tsn entry and also provide the path of install folder into the path of windows env variable

Connecting with Oracle
https://community.powerbi.com/t5/Desktop/Getting-Power-BI-Desktop-to-work-with-Oracle-ODAC-after-ver...

[nfietz]

@amitchandak Thanks for this response. Do you know why an improper install of the client would affect the 'DirectQuery' function but not 'Import?' Trying to understand the nuts and bolts behind this as well.

I'm working with our IT and DB teams on this, so will relay the info to them for us to try this. Have some folks out of the office, so will follow up after we've tested.