Power Query and the Oracle Ora-12154 error

0
votes

I can connect to the client's Oracle database using the full connection string in Power Pivot. However, I need to connect in Power Query which throws an error when attempting to use the full connection string because it exceeds the 128-character limit. So I attempted to use EZConnect and a TNS name in Power Query but both generate an ORA-12154 error message. I get the same error message when I attempt to use EZConnect or a TNS name in Power Pivot. a duplicate of the TNSnames.ora file is read successfully on at least one other machine on the same network.

The exact error message is:
"Unable to connect. We encountered an error while trying to connect. Details: 'Oracle: ORA-12154: TNS could not resolve the connect identifier specified"

.
SYSTEM DETAILS

OS
Windows 7 Enterprise 64-bit machine

Applications
Microsoft Excel 2013 32 bit

Oracle 11gR202 Client 32 bit
(from the client's approved installs site, i.e. not the version available from Oracle downloads)

Oracle 11gR202 Client 64 bit
(from the client's approved installs site, i.e. not the version available from Oracle downloads)

OraDB11g_home1
(this was the original installation from the oracle downloads site. during the installation process, the installer displayed many "file not found error messages," i assumed this meant that this version lacks a flag which marks it as approved for the client's system. so i installed the client approved versions above which resulted in being able to connect via Power Pivot using the full connection string).

instantclient_12_1 (C:\Program Files\instantclient_12_1)

Configurations
TNS_ADMIN
- System and User Variable(identical copies of a TNSnames.ora file is in both locations):
C:\oracle\network\admin;
C:\app\nd369d\product\11.2.0\dbhome_1\NETWORK\ADMIN

PATH
- System variable:
C:\Oracle\11gR202Client64bit\bin\;
C:\Oracle\11gR202Client32bit\bin\;
C:\ProgramData\Oracle\Java\javapath;
C:\Program Files\instantclient_12_1;
C:\app\nd369d\product\11.2.0\dbhome_1\BIN;
C:\oracle

JAVA
(i updated to Java 8 update 66 from the client's installs site. while the procedure installed the update, it didn't remove the earlier version so now both are present on the machine):
Java 7 Update 51 (64 bit)
Java 7 Update 51
Java 8 Update 66 (64 bit)
Java 8 Update 66

1
exceloraclepowerquerytnsora-12154
How are you using full connection string for Oracle on Power Query? Are you using an ODBC driver? - Oğuz Yıldız
I'm not well versed in ODBC connections, but I think the right answer is that I'm not using an ODBC driver; instead I'm using the OraOLEDB.provider from Oracle. - Tobias Cortese
It's just that, in Power Query, there's no way of giving the full connection string when using the built-in Oracle connector. To the "Server" parameter, you need to pass something like: hostname/servicename or TNS name. Similar to what you pass when connecting with sqlplus. - Oğuz Yıldız
Oh, yes. I answered without thinking. Sorry. I connect on other machines with both an EZConnect string and the friendly name I set up in the TNSnames.ora. Though I don't plan on setting up TNSnames.ora files on client machines because I'd like to keep the configurations as simplistic and agnostic as possible. - Tobias Cortese

1 Answers

0
votes

You're having multiple oracle clients installed, and very likely they have different tnsnames.ora and sqlnet.ora variants.

See also this: http://www.orafaq.com/forum/t/74793/

  • check if sql*plus works from the command line with a tns name
  • check your tnsnames.ora config file, I found it is quite error prone with those many parenthesis :)