Have a workstation that has several oracle clients (10.1.5, 10.2, 11.2). Also using the TNS_ADMIN environment variable. You can see these under the Connect Using: drop down, however the Database drop down does not have any of the choices that are in the tnsnames.ora file (same file others are using successfully) AND the check boxes for SQLNET Editor / TNSNames.Editor have red X’s by them instead of the green check marks.
I uninstalled Toad (12.1.0.22) and reinstalled no difference. Tried changing the TNS_ADMIN entry from C:\Oracle\product\11.2.0\client_2\network\admin, to C:\Oracle\product\10.2.0\client_2\network\admin, restarted TOAD, no difference.
I have checked across all the installed Oracle ‘products’ and the tnsnames.ora is the same with multiple entries.
Not sure why the Database list isn’t populating and the other two checkboxes have the red X’s…
Ideas?
pretty sure that was one of the items fixed by the 12.1.1 patch
I have checked across all the installed Oracle 'products' and the tnsnames.ora is the same with multiple entries.
Are you saying that you have multiple tnsnames.ora files? If you are using a TNS_ADMIN variable, then point it to a folder that is not part of your existing Oracle clients (maybe something like c:\Oracle\tnsadmin), and put your tnsnames.ora in that folder. The benefit of the TNS_ADMIN variable is so that you don't need a tnsnames.ora for every oracle client.
That said, I just tried pointing my tns_admin variable to the network\admin folder of one of my clients, and it worked fine for all of my installed clients, so I'm not sure why it isn't working for you.
If you WANT to have multiple tnsnames.ora files, one for each Oracle client, in the network\admin folder, that's fine, but in this case, you should not also have a TNS_ADMIN variable.
Thanks for the reply.
I happened to point the TNS_ADMIN back to what I thought it was originally (second paragraph in my post) and it is working now.
Interesting that this has nothing with Toad. In mine 5 years extensive work with Toad (all Oracle versions except 12c) Toad have never true problem with TNS names.
So for any problem like this, first try with plain SQL*Plus connection. If works then it is really Toad problem.
:-()
SQLPlus worked fine, it was a Toad problem
@bricot_750
Your last answer (which you mark as verified) was:
I happened to point the TNS_ADMIN back to what I thought it was originally (second paragraph in my post) and it is working now.
I really do like to understand how that was related to Toad. Please spent some additional time to help us understand the problem.
It would probably be more correct to say it is a problem that only seemed to affect Toad. The issue seems to have been something in that Windows Environment Variable path for TNS_Admin. We added this variable quite awhile ago for Toad when we have mutiple Oracle Home directories. This developer had 10.6 installed - working fine with the variable in place, they decided to upgrade to 12.1, ran the install and in 12.1 the Database drop down on the Session/New Connection screen was empty even though the TNSNames file was the same used by ver 10.6. The other odd thing was the Sqlnet editor and TNSNames editor had red X’s instead of the normal green check marks. I had copied the path for the environment variable path from another machine and emailed it to this machine for comparison, it looked correct. I then changed the oracle home to an Oracle 10.1.5 directory (again, the path in the TNS_Admin variable), didn’t fix it. I then changed it back by copying the path from the email, and as soon as I did this the Database drop down had values and the 2 editors had green check marks.
First let me thank you for your wider explanation. I’ll try to be the same in believe to clear up this problem.
>We added this variable quite awhile ago for Toad when we have mutiple Oracle Home directories
This is wrong and not true
- %TNS_ADMIN% affects all %ORACLE_HOMES% and all Oracle base software, so Toad who is only one of consumers reflect that problems immediately.
- Toad doesn’t needed %TNS_ADMIN% variable while ORACLE_HOME is pointing to correct settings (explained a little bit more later)
- If you run any Oracle software (like Toad as well) from Windows command prompt, where you previously set correct env, then you do not need TNS_ADMIN-try, you’ll be surprised.
>The other odd thing was the Sqlnet editor and TNSNames editor had red X’s instead of the normal green check marks.
So Toad alarmed you that something is wrong. And that is the beauty of Toad-he helps. Other Oracle software has low or no visual parts and only interaction is when you get thrown error. Then you are on your own.
%TNS_NAMES% env variable is not required and its role is that by it Oracle software knows where to find and assign network based settings (tnsnames.ora, sqlnet.ora, listener.ora, listener log and trace files etc). It is perfectly clear that for any active %ORACLE_HOME%, this location is default in %ORACLE_HOME%\network\admin directory. Problem arise when %ORACLE_HOME% is changed (because of multiple %ORACLE_HOMES%) or user want special location of mentioned files (like I have in %USERPROFILE%\Docummnets\Oracle\network\Admin). This is where TNS_ADMIN role play comes to live or (like in your case!) %ORACLE_HOME% “was/was not” defined so in all know location to Oracle, mentioned files were not there (i.e. tnsnames.ora file).
And for the end, many developers should read more carefully parts where Oracle explain setting multiple %ORACLE_HOME% environments (it is their need I know) because it is their job.
I hope mine standings is now more clear.
Hope this helps someone.
Old conversation, but I had a problem finding tsnames.ora yesterday on a co-worker's machine here in July 2021.
Turns out he had Windows File Manager hiding extensions, and the file as he saved it ended up as "tnsnames.ora.txt". Watch out for that, Windows users!
If I had a nickel for every minute I've spent working around Microsoft convenience features....
.....oh, wait; Bill Gates already does.
