Solved

tnsnames.ora not visible in Toad

Posted on 2012-03-16
15
10,666 Views
Last Modified: 2012-04-21
I have a machine which has tnsnames.ora and sqlnet.ora in the right places with right values. When I do Session/New Connections in TOAD, it has the proper Oracle home value under 'Connect Using'. However, I see the 'SQLNET Editor' button enabled with green check but the 'TNSNames Editor' button is disabled. I was thinking that if Oracle home is properly defined, it knows where to look for tnsnames.ora.

Any ideas?
0
Comment
Question by:soccerplayer
  • 7
  • 6
  • 2
15 Comments
 

Author Comment

by:soccerplayer
ID: 37731457
My Oracle is installed on Windows.
0
 
LVL 23

Expert Comment

by:David
ID: 37731516
You need the TNS_ADMIN variable defined either at the system or user level.  Confirm there is no value first by going to a command prompt and running SET to list the in-use variables.  It should look something like $ORACLE_HOME/network/admin.

TNS_ADMIN is set under the Control Panel | System | Advanced | Environment Variables.

Reboot the PC in order to enable the string.  TOAD should then recognize the variable and map it.
0
 

Author Comment

by:soccerplayer
ID: 37731768
When I do 'set' at Windows command prompt, I don't see either ORACLE_HOME or TNS_ADMIN.
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 23

Expert Comment

by:David
ID: 37731771
My bad, you would have ORACLE_HOME only if the database software was installed.  You can execute a search on "tnsnames.ora"; set TNS_ADMIN to its drive:path name.  Make sense?
0
 

Author Comment

by:soccerplayer
ID: 37731786
I don't have TNS_ADMIN defined anywhere in my machine but Toad is working fine. It is somebody else's machine that I am trying to get working.
0
 
LVL 16

Expert Comment

by:Wasim Akram Shaik
ID: 37732191
I dont think that toad would look for a TNS_ADMIN variable..

The problem you might be having here is because of the tnsnames.ora file not being present in your $ORACLE_HOME/Network/Admin Path


As dvz suggested, Place tnsnames.ora file over the TNS_ADMIN path, toad will automatically detect that file and that button would get automatically enabled..

But yes, you have to make sure that your ORACLE_HOME variable is set properly..

but dvz, even if the client is present then ORACLE_HOME variable gets set right, we don't need the database software at all times..
0
 
LVL 16

Expert Comment

by:Wasim Akram Shaik
ID: 37732196
Also I am sure, if toad is working fine as author had mentioned.., I think, there is an oracle client, which is allowing himto connect(or may be toad itself is having a built in client)
0
 

Author Comment

by:soccerplayer
ID: 37733767
When I do:
set

in Windows command prompt, the tnsnames.ora does not exist in the Oracle path but I can connect to Oracle fine. I also don't have TNS_ADMIN defined anywhere (not in System/Advanced/Environment Variables). It works perfectly fine on my machine. How can that be?
0
 
LVL 16

Expert Comment

by:Wasim Akram Shaik
ID: 37734141
When I do:
set

in Windows??

What have you set?? didn't get you...

There is no need of TNS_ADMIN, ORACLE_HOME has to be defined and it should be present in environment variable, thats enough for an oracle client to connect to oracle.. by default toad also uses the same oracle client to connect to oracle and to make use of TNSNamesEditor in toad, you have to place the tnsnames.ora file in the $ORACLE_HOME/Network/Admin path(which is referred as TNS_ADMIN),
0
 

Author Comment

by:soccerplayer
ID: 37740268
I have Oracle server on a Unix box and Oracle client on Windows. In Windows, under the results of the 'set', command I don't have ORACLE_HOME or TNS_ADMIN defined. In System/Advanced/Environment Variables, I don't have ORACLE_HOME or TNS_ADMIN defined. In my path, I have:

D:\oracle\product\10.2.0\client_2\bin;

I have tnsnames.ora and sqlnet.ora defined in both D:\Oracle\product\10.2.0\client_2\NETWORK\ADMIN\ and D:\Oracle\product\10.2.0\client_2\NETWORK\ADMIN\SAMPLE\.

In Unix, when I do:
echo $ORACLE_HOME
I see the whole path for Oracle, i.e., ../oracle/oratools/product/10.2.0.

On the Unix server, I have tnsnames.ora and sqlnet.ora defined in both .../NETWORK/ADMIN/ and .../NETWORK/ADMIN/SAMPLE/. This is visible to other users as well.

In my Toad, I see the TNSNames Editor button fine. This other user doesn't.
0
 
LVL 16

Expert Comment

by:Wasim Akram Shaik
ID: 37740649
---In my Toad, I see the TNSNames Editor button fine. This other user doesn't.

The User which doesn't see the button.. does he has the same configuration which you have mentioned in the steps above in windows machine..

We don't have to check anything at server end for this particular issue.

On the user machine where in Toad is unable to use TNSNames Editor, create the environment variables ORACLE_HOME and assign it the location/path where the oracle client is installed.

after this restart the system.. this should resolve your problem..
0
 

Author Comment

by:soccerplayer
ID: 37741962
How come I don't have ORACLE_HOME and TNS_ADMIN defined and everything works fine? Just curious.
0
 
LVL 16

Expert Comment

by:Wasim Akram Shaik
ID: 37741982
I am not sure how its working fine even if you don't have these things defined..

but defining the  ORACLE_HOME will resolve this problem

May be i think because of the ORACLE_HOME Path defined in the PATH (D:\oracle\product\10.2.0\client_2\bin) is doing the trick for you..

Else, you could do the same thing of pointing the path to ORACLE_HOME in the other system where Toad is not working and can see whether the same thing makes the toad to work for you(for tnsnames editor button)
0
 

Author Comment

by:soccerplayer
ID: 37755579
Issue is not resolved yet. Getting the error:

Toad error - Can't initialize OCI. Error -1

Researching...
0
 
LVL 16

Accepted Solution

by:
Wasim Akram Shaik earned 500 total points
ID: 37755726
hold on...!!!

let me the versions of Windows and Toad, by the past posts of yours i can see that your oracle is 10g.

i think this(Toad error - Can't initialize OCI. Error -1 ) is more of a compatibility issue nothing related to tnsnames.ora.
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Oracle 10g - insert string with special characters 8 78
sort a spool into file output in oracle 1 44
constraint check 2 48
Oracle dataguard 5 45
Introduction A previously published article on Experts Exchange ("Joins in Oracle", http://www.experts-exchange.com/Database/Oracle/A_8249-Joins-in-Oracle.html) makes a statement about "Oracle proprietary" joins and mixes the join syntax with gen…
I remember the day when someone asked me to create a user for an application developement. The user should be able to create views and materialized views and, so, I used the following syntax: (CODE) This way, I guessed, I would ensure that use…
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.

856 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