Couldn't use TNSNames to connect, and TNSNames Editor was greyed out
Today I ran into much tnsnames related frustration configuring and getting information out of Toad for Oracle on Windows XP.
The Actual Problem:
Issue appears to be due to Toad looking for tnsnames.ora in the main client directory (as in InstantClient) as opposed to the \network\admin directory (for a full client install). Resolved by setting TNS_ADMIN environment variable.
How to:
An Oracle Client install is required before installing Toad (for tnsnames connectivity). The upshot is that the small amount of documentation that there is, applies to the Oracle Instant Client and not to the Full Client, and Toad help does not appear to have been updated to reflect changes in the latest release. My problems turned out to be minor issues, but since the documentation is scant and makes a lot of assumptions, I've listed basic install/configuration info below.
These steps apply to Toad for Oracle 9.7.2 and Oracle Client 11.1 on Windows XP
- If you do not already have an Oracle Client installed download the Instant Client
- It will also be helpful to download some of the associated extensions to the package such as SqlPlus and ODBC
- If you do not already have source for a licensed version of Toad, download the evaluation version
- Install and/or configure the Oracle Client
- If using InstantClient
- Extract the .zip file to a local directory (there are no requirements around the top level location, use one that makes sense to you, but do not change the name of the subdirectories it exports).
- Verify that the tnsnames.ora is in the working directory of the base of the extracted directory.
- If it doesn't exist and this is a new install create a text file 'tnsnames.ora'
- If it doesn't exist and you have a full client install, or an existing InstantClient install, locate the tnsnames.ora (default location is %ORACLE_HOME%\network\admin) and make a note of it.
- For both the full client and the InstantClient you will need to set environment variables
- Right click 'My Computer' and select 'Properties'
- Select the 'Advanced' tab and click the 'Environment Variables' button
- In 'System Variables' locate and select the PATH variable.
- Click the 'Edit' button and verify the existence of, or append the client directory location to the end of the path.
- To add, append a semi colon followed by the path to the client directory (e.g. ;c:\instantclientdir)
- If you are using the full client, and the tnsnames.ora file is in a location other than the working directory, select 'New' to add a system variable 'TNS_ADMIN'
- In 'Variable Name' enter TNS_ADMIN
- In 'Variable Value' enter the directory location of the tnsnames.ora file. (e.g. c:\instantclientdir\network\admin)
- Verify paths are set (both Instant Client and full Client)
- In a cmd window type
- echo %PATH%
- This should return your normal path with the location of the client files appended.
- Verify TNS_ADMIN variable (Full Client and clients with existing tnsnames.ora)
- In a cmd window type
- Echo %TNS_ADMIN%
- This should return the location of the tnsnames.ora
- Install Toad
- From the Toad install media or download kick off setup
- No changes from defaults are required during the actual install, choose components and file locations as best suits your environment and licensing.
- Configure/Verify Toad
- With the Oracle Client installed and configured, Toad should be able to locate the settings it needs
- To verify select 'Session' new connection. In the 'Installed Clients' box (bottom right)
- Verify that a client is listed
- Click the elipses button to the right and verify that there are no errors listed (they will be red).
- If values are incorrect, it is most likely an incorrect path in the environment variables.
- From a cmd window type
- env
- Check that the path and TNS_ADMIN variable are correct, and verify there aren't stray oracle client variables with incorrect values (e.g. an incorrect ORACLE_HOME). Be very careful about changing these values if you have an existing client install.
- Missing values not related to the ORACLE_HOME are not an issue if you have client only install (you don't need a SID etc. unless you have a local database).
- To verify/configure the TNSNames Editor Utility:
- On the 'Utilities' menu select TNSNames Editor.
- If it is greyed out on the menu there is still a path issue (see above)
- If you created a blank file it will be empty and entries can be added either by editing text directly or by generating entries from direct connections.
- To add an entry click the 'Add Service' button (looks like a shiny blank sheet)
- Enter connection information.
- To enter the SID as SID instead of Service, check 'Use Oracle 8 and previous identification (SID)'
- To add Oracle Utilities to Toad
- On the 'View' menu select 'Toad Options'
- In the left hand pane, Select 'Executables'
- Enter the path for any Oracle Utilities you have installed or downloaded.
- ToadWorld -- of these links I thought ths site's tips and tricks had lots of great, brief, articles.
- Ask Toad -- I didn't find much content here, but I may be looking in the wrong place
- Toad Yahoo developer community
