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

[anitaberg]

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) ) )

[jessica-ko]

THANK YOU SOOOOOOO MUCH!!! worked for me

[ToddChitt]

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]

@tjukken,

Try to change credential type in data source settings dialog.

[tjukken]

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]

@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

[v-chuncz-msft]

@tjukken,

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