Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
tjukken
Regular 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

27 REPLIES 27

Figured it out now: I kept the installation with having ODAC installed to client_1. I de-installed the store version and installed the download version. I new error related to TNS names Oracle: ORA-12541: TNS:no listener" when I connected to the computername as server name in Power BI. I checked the tnsnames.ora in C:\app\client\anitaberg\product\12.2.0\client_1\Network\Admin and then tested to log on as localhost as server name. Got then the error Details: "Oracle: ORA-12504: TNS:listener was not given the SERVICE_NAME in CONNECT_DATA" , then I used the name of the service (if it is called that) ORCL as server, and then Power BI was able to show the three of tables.

 

 

ORCL = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = orcl) ) )

THANK YOU SOOOOOOO MUCH!!! worked for me

Curious to see if anyone has a definitive resolution to this issue. Here is my scenario:

  • Using laptop A with Oracle drivers installed, I connect VPN to client system
  • I open Power BI x64 and open a PBIX model. I can refresh data connected to the client's Oracle database server, using Database level credentials.

All is good so far.

  • Using laptop B with same version of Oracle drivers installed, I connect to same VPN using same credentials.
  • The TNSNAMES.ORA file is copied to appropriate place on machine B
  • I open the same Power BI PBIX file, but this time a data refresh fails.
  • I assume it is due to the fact that the Oracle credentials are not saved.
  • I start a clean, new Power BI model
  • I get into Data Source Settings and delete any and all Oracle connections
  • Starting fresh, I create a connection to Oracle, specify the same server, user and password.
  • I get the error: "An error happened while reading data from the provider: 'Object reference not set to an instance of an object' "

 

Any help would be appreciated. Thanks in advance.

v-chuncz-msft
Community Support
Community Support

@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.

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

@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

@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.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.