Oracle: ORA-12154: TNS issue: Excel 64-bit/Windows

Question

I'm under Windows 10 64-bit with Office 2016 64-bit. Just need to connect to an Oracle 11g (11.2.0) database using Excel (to use Power Query/ BI).

First it was installed: Oracle Database 11g Release 2 Client (11.2.0.1.0) for Microsoft Windows (x64) (https://www.oracle.com/technetwork/database/enterprise-edition/downloads/112010-win64soft-094461.html)

I got an error of missing 64-bit components then I installed: 64-bit ODAC 11.2 Release 6 (11.2.0.4.0) Xcopy for Windows x64 (https://www.oracle.com/technetwork/database/windows/downloads/index-090165.html)

and now there error is: "[DataSource.Error] Oracle: ORA-12154: TNS:could not resolve the connect identifier specified"

I already did same thing using Windows 7 64-bit HOWEVER with Office 2016 32-bit edition and so does oracle client and it was not installed ODAC. And it used to work so great.

I have same TNSNAMES.ORA file with all entries fine because I know that it works for office 32-bit. So TNSNAMES.ORA file won't be the issue (location> C:\oracle\product\11.2.0\client_1\Network\Admin).

I tried a lot of things from Stack posts like, Environment Variable, registry changes, installation check... but I'm really not getting it. Just need Excel 64-bit to connect with oracle 11g, just that but only 32-bit works...

It seems a so specific issue and it is driving me nuts. Please if someone can help please me using an "easy" explanation I'll really appreciate a lot.

Answer 1

It will be hard to help you because you did not provide so much information, so I will give some more generic hints.

All components have to be in the same architecture. If your Excel/Office is 64-bit then the Oracle Client and the ODAC have to be also 64-bit. The database can be either 32-bit or 64-bit in any case.

When you like to connect from Excel to an Oracle database you can use either ODBC driver or OLE DB driver. Both are available from Microsoft and from Oracle. As you did not mention any ODBC I assume you use the OLE DB driver.

The Microsoft ODBC and OLE DB drivers both comes with your Windows, however they are old (ODBC is deprecated and OLE DB is deprecated) and they exist only for 32-bit. That's the reason why you can connect from 32-bit Excel to Oracle just with an Oracle InstantClient.

If you are working on 64-bit then you have to use the Oracle drivers, which also means you have to install it separately as they are not part of standard Windows installation - that's the ODAC package you installed.

Now, as you get an ORA-12154: TNS:could not resolve the connect identifier specified error your installation seems to work in general. The different drivers from above have different paths to find the tnsnames.ora file, see Determining location of relevant tnsnames.ora file

Create an Environment variable TNS_ADMIN=C:\oracle\product\11.2.0\client_1\Network\Admin as the Environment variable seems to have the highest precedence over all other settings. I think the you application should be able to resolve the alias. Otherwise the data in your tnsnames.ora file might be wrong (perhaps a typo)