Forum Discussion
Oracle connection error: Object reference not set to an instance
Ok, thanks. I created a support ticket now, I'll keep you posted.
That would be great!
My support ticket still remains unresolved, I still cannot connect to Oracle DB with 64-bit PBI desktop. Since I managed to get the on-premises gateway working, here's the partial solution,
Power BI requires the Oracle Data Provider for .NET 4 (unmanaged driver) in order to be able to connect to an Oracle DB. If you want to reduce installation size, you may use the Xcopy package, just pay attention to the installation instructions. (At first, I neglected to put Oracle installation directory to PATH, which is why my gateway did not work.) After the driver is installed and configured, just restart PBI desktop or on-premises gateway service, so that the environment variables are refreshed and Power BI is able to locate the freshly installed driver.
This is not the case with 64-bit PBI desktop, however. It seems to be a unique snowflake in a sense, that it does not use PATH for locating the driver. I believe it requires the driver to be installed in GAC - basically a directory for shared DLLs and other assemblies located under %WINDIR%\Microsoft.NET\assembly. When Oracle universal installer prompts whether to place the driver in GAC and configured on machine level, select yes. If using Xcopy version, use configure.bat in Oracle install directory to place the driver there.
When the driver is not in GAC, 64-bit PBI desktop gives me ‘System.Data.OracleClient requires Oracle client software version 8.1.7 or greater’ error. After it is placed there, I get the all too familiar ‘Object reference not set to an instance of an object.' exception. Am I still missing a step somewhere?
This is not the case with 64-bit PBI desktop, however. It seems to be a unique snowflake in a sense, that it does not use PATH for locating the driver. I believe it requires the driver to be installed in GAC - basically a directory for shared DLLs and other assemblies located under %WINDIR%\Microsoft.NET\assembly. When Oracle universal installer prompts whether to place the driver in GAC and configured on machine level, select yes. If using Xcopy version, use configure.bat in Oracle install directory to place the driver there.
While the above holds true for the version from Microsoft Store, it appears the version downloadable from https://www.microsoft.com/en-us/download/details.aspx?id=45331 works fine (both February 2018). So finally, I have the driver installed from Xcopy as described in my previous message and I'm able to connect to the Oracle DB in 64-bit PBI Desktop, too.
My take on the situation is that MS Store version disregards PATH and goes straight to GAC and fails. The "direct download" searches PATH first and then goes to GAC - if I remove my ODAC directory from PATH, this one throws the same ‘Object reference...' exception.
If anyone has managed to get the 64-bit version from Microsoft Store working with Oracle, it would be interested to know how.
Thank you! I had the same error ("Object reference not set to an instance").
In my case it didn't work with Power BI downloaded from Microsoft Store either.
I was googling hard but couln't find any download link or then I was redirected to the store. I usually had the web version downloaded but this options seems to be removed for updating. So thanks for the link!
For the record and people having the same troubles I had:
I was using Power BI from the Microsoft Store and was trying to connect to an oracle database.
However, I had the error "The recommended provider ('Oracle.DataAccess.Client') is not installed."
So I can confirm:
- download ODAC121024_x64.zip from https://www.oracle.com/technetwork/database/windows/downloads/index-090165.html (64bit in my case)- unzip and run the setup.exe (I had it run with default settings)
- Then, if you are able to connect but get the object reference error, deinstall Power BI Microsoft Store version, download from the web and run this version.
- trying to connect again to my oracle database. What a surprise, it worked!
Best,
Trojii
I have the same issue. PBI Desktop works fine but can't get past the error when setting up the data source in the gateway.