Oracle connection error: Object reference not set to an instance

[tjukken]

Hi

I'm trying to connect to an Oracle 11g database (not local), but I do only receive an error message. First I write in the servername/ip-address/tnsnames on the Oracle server and then I'm displayed with a new box and I write in the database username and password. The I got the error saying: "Object reference not set to an instance...." (see more details from the mash-up / log file below). Quit poorly description so there is not that much clue on how to solve the issue.

I have followed the documentation at: https://docs.microsoft.com/en-us/power-bi/desktop-connect-oracle-database . I installed the Oracle driver: 64-bit ODAC 12c Release 4 (12.1.0.2.4) for Windows x64 (ODAC122010_x64.zip) and restarted my computer after this process.

I have tried to connect to the database with the IP-address/SID and the tnsnames.ora approach, but nothing works (same error). I have also tried on two different client computers (same error).

I have tried to connect with Oracle SQL developer to the database and that works. 

Is there something I have missed? 

Got the lastest PBI Desktop (december version).

DataMashup.Trace Error: 24579 : {"Start":"2017-12-22T09:38:58.1118788Z","Action":"Engine/IO/Db/Oracle/Connection/Open","HostProcessId":"5188","ConnectionId":"ec5a6fb1-f5c8-496a-b39b-ed59e19b1e5d","Exception":"Exception:\r\nExceptionType: System.NullReferenceException, mscorlib, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b78a5c561934e019\r\nMessage: Object reference not set to an instance of an object.\r\nStackTrace:\n   at Oracle.DataAccess.Client.OracleException.get_Number()\r\n   at Oracle.DataAccess.Client.OracleException.HandleErrorHelper(Int32 errCode, OracleConnection conn, IntPtr opsErrCtx, OpoSqlValCtx* pOpoSqlValCtx, Object src, String procedure, Boolean bCheck, Int32 isRecoverable, OracleLogicalTransaction m_OracleLogicalTransaction)\r\n   at Oracle.DataAccess.Client.OracleException.HandleError(Int32 errCode, OracleConnection conn, IntPtr opsErrCtx, Object src, OracleLogicalTransaction m_oracleLogicalTransaction)\r\n   at Oracle.DataAccess.Client.OracleConnectionOCP.Open(OracleConnection con)\r\n   at Oracle.DataAccess.Client.OracleConnection.Open()\r\n   at Microsoft.Mashup.Engine1.Library.Common.TracingDbConnection.<Open>b__0(IHostTrace trace)\r\n   at Microsoft.Mashup.Engine1.Library.Common.Tracer.<>c__DisplayClass4.<TracePerformance>b__3(IHostTrace trace)\r\n   at Microsoft.Mashup.Engine1.Library.Common.Tracer.TracePerformance[T](String method, Func`2 func)\r\n\r\n\r\n","ProductVersion":"2.53.4954.621 (PBIDesktop)","ActivityId":"2120ce79-b905-4d70-aae8-24d67207c9ed","Process":"Microsoft.Mashup.Container.NetFX40","Pid":11320,"Tid":1,"Duration":"00:00:00.0813127"}

Best regards

Andy

[pabeader]

If you are using the Marketplace version of Power Bi Desktop and getting this error, try this:

In your oracle client home directory you should be able to find oraons.dll.  Copy this file into the \bin folder of the same directory.

Close any open Power BI Desktop and reopen it.

You should be able to connect now.

This worked for me.  I found it buried in the install instructions for Oracle on Power BI on Microsofts site.

For me the directory where I found the file was:

c:\app\client\OraHome

and I copied it to 

c:\app\client\OraHome\bin

Hope this helps somebody!

[rray9895]

Oh my goodness, you just saved my whole day. I was so flustered trying to figure out what broke.

[robertd3wk]

worked for me, thanks!

[Anonymous]

Hi pabeader,

Thank you for your advice.  I've been battling with this for long!

[Anonymous]

I got the same error. When I am trying to connect the Oracle database for my new dashboards I am able to connect it. But when I am trying to connect the oracle database to my old existing dashboard, I was getting "Object reference not set to an instance" error. This was due to different version of Power BI that I used while creating my old dashboard.

"This issue resolved when I downloaded the latest version of Power BI and opened my old dashboard with the latest  Power BI version"

Please try to download the latest Power BI version and check if this helps to resolve your issue.

[Anonymous]

Installing the Web vs the Store version fixed the problem for me as well!

For those wondering, here is the link to directly download from the web: https://www.microsoft.com/en-us/download/details.aspx?id=58494

Do not download from the Microsoft Store.

[NicDuf]

You made my day!

[Anonymous]

I am experiencing the same issue. I installed Oracle client and still does not look like it is resolved. what am I doing wrong?where should I place the client_1 file?

[ThierryG]

Same problem and same solution worked for me too i.e after installing the dowloadable version (and even keeping the store version in parallel).

[thymallus]

 Hi Andy,

Did you manage to solve this issue?

Likewise, I'm also trying to connect to an Oracle 11g database with drivers from the 64-bit ODAC 12c Release 4 (12.1.0.2.4) for Windows x64 (ODAC122010_x64.zip) and I'm getting the same "An error happened while reading data from the provider: 'Object reference not set to an instance of an object.'" error. This happens in Power BI Desktop (Februrary 2018 64-bit) as well as when trying to setup a new data source for on-premises data gateway. I do have tnsnames.ora defined and the connection defined there works fine with SQLPlus. As for Power BI, both tnsnames.ora and IP/SID produce the same error.

Using the 32-bit version of Power BI desktop and 32-bit drivers from ODAC 12.2c Release 1 and Oracle Developer Tools for Visual Studio all is working well. The problem is, I really do need the 64-bit drivers for the on-premises data gateway.

[tjukken]

No, I didn't proceed with the issue in this case. We created a (poor) workaround with CSV-files.

Good luck. 🙂

[thymallus]

Ok, thanks. I created a support ticket now, I'll keep you posted.

[tjukken]

That would be great!

[thymallus]

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?

[ataft]

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.

[thymallus]

---

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.

[Anonymous]

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

[willike_at_oocl]

+1 on Trojii's solution.  I had a clean windows 10, odac 64bit installed via setup.exe (not xcopy) and store version of PowerBI running on intel i3-7100 64bit.   Store version about screen was reporting a 64bit installation... After uninstalling store version and installing from https://www.microsoft.com/en-us/download/confirmation.aspx?id=45331, it worked.

[fifkafifka]

Trojii's solution worked for me. I had windows store powerbi desktop, odac 64bit and odac 32bit installed and still couldn't connect to oracle db. i uninstalled store app, installed the web version and everything started to work.

[anitaberg]

Hi! I have been experiencing with this ODAC install and related issues. I have a complete Oracle test environement on my virtual machine where I am trying to install ODAC. I have the Oracle database server and OBIEE (Oracle BI, with servers).

Due to this I had a client_1 in C:\app\client\<windowsuser>\product\12.2.0 folder.

1. The first installation I installed odac in a client_2 folder. Result: Power BI did still complain about not having ODAC installed

Then I found a thread https://community.powerbi.com/t5/Desktop/Unable-to-connect-using-Oracle-database-even-after-ODAC-ins... saying that installing on the same client would solve issue 

2. Changed the path for ODAC install to client_1

Then I no longer got the error abot having to install odac client, but the error Details: "An error happened while reading data from the provider: 'Object reference not set to an instance of an object.'"

Found some stackoverflow saying that this issue is mixing 32 bit and 64 bit, and also Oracle documentation does not recommend installing onto existing oracle home/folder.

I have not installed using x_copy so far. But for the last installation I did select machine level for .NET I have now the store version of Power BI, but will install with the other downloadable.

QUESTION: What should I do to get this to work, having the downloadable version instead? Go back to client_2 folder installation, or use X_copy for ODAC to install instead? Where do you install ODAC?