Solved

Getting ORA-12154 error with Oracle data connection in Excel 2007

Posted on 2011-03-22
7
2,196 Views
Last Modified: 2012-05-11
I'm attempting to set up an Oracle database connection in Excel 2007. To do this, I'm selecting "From Other Sources" from the "Data" menu. I then select "From Data Connection Wizard". In the wizard, I select "Other/Advanced". The Data Link Properties dialog opens. I select "Oracle Provider for OLE DB" as the Provider. On the "Connection" tab, I enter the name of the database server into the "Data Source" field, the User Name and the Password. I then test the connection. I was able to get this working once, and I then saved the connection as an ".odc" file. However, since that one time of getting it to work, I have been unable to get it working again, even though I'm entering the exact same connection information. I consistently get this error:

"Test connection failed because of an error in initializing provider. ORA-12154: TNS:could not resolve the connect identifier specified"

I get the same behavior if I select "Microsoft OLE DB Provider for Oracle" as the provider. What gives? Is there some setting in Excel that I need to set to get this to work? It worked once, so it should work again, right?

On a related issue, when I got it working the first time, it gave me a list of tables from the database to which I could connect as the data source. However, I want to pull data from a VIEW, not a TABLE. I thought I could do this through VBA code, but I couldn't get the Oracle connection to work there, either.
0
Comment
Question by:DanielBorson
  • 5
  • 2
7 Comments
 
LVL 7

Expert Comment

by:Jignesh Thar
Comment Utility
It is possible that Oracle schema that you configured is no more available from your machine. Can you Tnsping the oracle data source?

I would suggest you to check host entry in tnsnames.ora file and ensure that it is configured properly.
0
 

Author Comment

by:DanielBorson
Comment Utility
I can connect through all my other apps (SQL Developer, SQL Plus, etc.), and I can successfully "tnsping" that database server. I've just updated all my tnsnames.ora files, and they're all configured properly. Why could I connect to the database, and then an hour later it doesn't work? And in that hour, I've changed no tnsnames.ora files, or anything else?
0
 
LVL 7

Expert Comment

by:Jignesh Thar
Comment Utility
DanielBorson - Ok in that case , I am sure what is causing this. Does connection work from access?
0
Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

 

Author Comment

by:DanielBorson
Comment Utility
I'm trying to connect through Access, and here's the behavior I'm seeing:

I select "Get External Data - ODBC Database".
I'm attempting to set up a "Machine Data Source". If I select as a driver "Microsoft ODBC for Oracle", I get the same 12154 error.
If I use the "Oracle in OraClient10g_home1" driver, I do not see any of my tnsnames entries in the dropdown for "TNS Service Names" in the Oracle ODBC Driver Configuration dialog, and entering the TNS Service name results in the same 12154 error.
If I use the "Oracle in OraHome92" driver, I DO see all my servers from the tnsnames in the dropdown list, but I still get the 12154 errror when attempting to test the connection.

It's very difficult to tell just which tnsnames.ora file is being used for what, but they've all been updated, so it should connect. It also doesn't make a difference which server I try to use, it never works.
0
 

Author Comment

by:DanielBorson
Comment Utility
Is anyone able to give me a clue as to what's wrong or how to troubleshoot this? Does anyone know specifically which Oracle driver I should use? Does anyone know which "tnsnames.ora" file Excel 2007 uses? Does that depend on the driver? Why are there more than one Oracle driver available in Excel? What's the difference? Finally, how do I get this work?
0
 

Accepted Solution

by:
DanielBorson earned 0 total points
Comment Utility
I was able to get this working by adding a "LISTENER" line to the tnsnames.ora file. The line looks like this:

LISTENER_DSN = (ADDRESS=(PROTOCOL = TCP)(Host = servername)(Port = 12345))

where "DSN" is the service name of the database in the tnsnames.ora file. Once I did this, I could connect from Excel or Access through an ODBC connection or from Excel through an OLE DB for Oracle connection. There needs to be one LISTENER line for each service name for which you want to do this.

0
 

Author Closing Comment

by:DanielBorson
Comment Utility
No one provided me with a solution, and I figured it out on my own, but thought others could benefit from my experience.
0

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Introduction This Article briefly covers methods of calculating the NPV and IRR variants in Excel as well as the limitations in calculating and interpreting IRR results. Paraphrasing Richard Shockley, author of my favourite finance reference tex…
Technology opened people to different means of presenting information, but PowerPoint remains to be above competition. Know why PPT still works today.
The viewer will learn how to simulate a series of sales calls dependent on a single skill level and learn how to simulate a series of sales calls dependent on two skill levels. Simulating Independent Sales Calls: Enter .75 into cell C2 – “skill leve…
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now