cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
tjukken Frequent Visitor
Frequent Visitor

Oracle connection error: Object reference not set to an instance

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

16 REPLIES 16
Community Support Team
Community Support Team

Re: Oracle connection error: Object reference not set to an instance

@tjukken,

 

Try to change credential type in data source settings dialog.

Community Support Team _ Sam Zha
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
tjukken Frequent Visitor
Frequent Visitor

Re: Oracle connection error: Object reference not set to an instance

Thanks for your answer, but I need to be sure that I understand your suggestion correctly.

Would you like me to use Windows credientals instead of SQL / Basic identification? 

 

Best regards

Andreas H

tjukken Frequent Visitor
Frequent Visitor

Re: Oracle connection error: Object reference not set to an instance

@v-chuncz-msft

 

I haven't anything more from you regarding this case and I'm still stuck / can't connect to the database with PBI desktop. I have spoken to the client and they have no intension adding authentication via an Windows user/AD-solution (like this: https://docs.oracle.com/database/121/NTQRF/active_dir.htm#NTQRF270).

Is there any other way to solve this? More debugging?

 

Best regards

Andreas H

Community Support Team
Community Support Team

Re: Oracle connection error: Object reference not set to an instance

@tjukken,

 

I can not reproduce the issue. You could also create a support ticket.

Community Support Team _ Sam Zha
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
thymallus Frequent Visitor
Frequent Visitor

Re: Oracle connection error: Object reference not set to an instance

 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 Frequent Visitor
Frequent Visitor

Re: Oracle connection error: Object reference not set to an instance

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

 

Good luck. Smiley Happy

thymallus Frequent Visitor
Frequent Visitor

Re: Oracle connection error: Object reference not set to an instance

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

tjukken Frequent Visitor
Frequent Visitor

Re: Oracle connection error: Object reference not set to an instance

That would be great!

thymallus Frequent Visitor
Frequent Visitor

Re: Oracle connection error: Object reference not set to an instance

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?