topic Re: Oracle DB connection in Desktop

Oracle DB connection

apsaunders5 — Mon, 19 Feb 2018 21:13:19 GMT

I have read through the various threads on here to identify recommended steps, to no avail. I am trying to connect Power BI desktop to an oracle DB for reporting.

Power BI version: 2.55.5010.641 (PBIDesktop) (x64)

Oracle Client Version: 64-bit ODAC 12c Release 4 (12.1.0.2.4) for Windows x64

TNS file was modified during installation to include target DB information. When navigating to the TNS file, I see that it was modified successfully. When using Power BI to establish the connection, I continue to receive the following error message:

Details: "Oracle: ORA-12504: TNS:listener was not given the SERVICE_NAME in CONNECT_DATA"

Any insight would be greatly appreciated. I will also add that I am not the owner of this DB nor do I manage it. I own development for the overlying application.

Andrew

Re: Oracle DB connection

Anonymous — Wed, 21 Feb 2018 07:00:42 GMT

apsaunders5,

Are you able to connect to Oracle from SQLPLUS or SQL developer? Make sure that SQL developer or SQLPLUS is installed in the same machine as Power BI Desktop.

In addition, what is the format of connection string do you use in Power BI Desktop?Have you tried to use host: port/servicename? Also please check if your firewall blocks the port.

There is also a similar blog for your reference.
https://oracletempspace.wordpress.com/2011/05/15/error-ora-12504-tnslistener-was-not-given-the-service_name-in-connect_data/

Regards,
Lydia

Re: Oracle DB connection

apsaunders5 — Tue, 27 Feb 2018 16:26:23 GMT

Lydia - Thanks for the response. Yes I am able to connect with SQL developer. Firewall is not blocking the port. When attempting to "GetData" from Oracle DB, I provide the server FQDN as the only input. Is there another option for an Oracle connection string in PowerBI? I noticed the input was different for "GetData" against SQL Server. I will reference the blog post in the meantime.

Andrew

Re: Oracle DB connection

rocky09 — Tue, 27 Feb 2018 16:44:23 GMT

seems, same problem as yours and resolved

Link

Re: Oracle DB connection

apsaunders5 — Fri, 02 Mar 2018 17:43:26 GMT

Did not resolve the issue, but thank you for the suggestion. Looking into TNS_ADMIN

Re: Oracle DB connection

FredTheFrog — Sat, 03 Mar 2018 01:44:37 GMT

From a command prompt, can you successfully TNSPING the SID name of the database? If not, your tnsnames.ora file isn't configured correctly. Once you can successfully TNSPING the SID name, then just put only the SID name in the form. I'm currently using Oracle XE on my local laptop, and enterprise networked Oracle 12 at work, and they both connect quite simply and easily.

C:\Users\FredTheFrog>tnsping XE
TNS Ping Utility for 64-bit Windows: Version 11.2.0.2.0 - Production on 02-MAR-2018 20:42:52
Copyright (c) 1997, 2014, Oracle. All rights reserved.

Used parameter files:
C:\oraclexe\app\oracle\product\11.2.0\server\network\admin\sqlnet.ora

Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = Fred)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = XE)))
OK (10 msec)

C:\Users\FredTheFrog>