I couldn’t figure out how to connect to my remote Oracle install on a VMWare Solaris install using TOAD until I found this page at trap17. Turns out you need to install the Oracle Instant Client (or at least have it installed somewhere) with the correct environment variables for TOAD to pick up your Oracle install. I followed this guide and it worked straight away:
Here’s how you do it (Although I’ve done my best to present an accurate procedure, I can’t guarantee this will work for everyone. Messing around with your environmental variables is not for the weak, proceed with caution):
1) Prepare your mind for information that should be used once and then quickly discarded. Otherwise the following information may stick to your brain and take up valuable space. You are warned.
2) Install the oracle instant client. I don’t know why…I kinda wish I did. You probably can get away with installing just the basic package. Unzip the files to a directory, and then copy the path of this directory to the clipboard.
3) Add this directory to the PATH environmental variable. To do this, right click on My Computer, go to the Advanced tab, and click ‘Environment Variables’. Under System variables, find ‘Path’. Select it, and click edit. ctrl-v to paste the directory into the string, and say OK. e.g.:
Before: %SystemRoot%system32;%SystemRoot%;C:Program FilesATI TechnologiesATI Control Panel;
After: %SystemRoot%system32;%SystemRoot%;C:Program FilesATI TechnologiesATI Control Panel;C:instantclient;
4) In the same window, under ‘User variables for …’, Click the New button. Call the variable ‘TNS_ADMIN’. ctrl-v to paste the directory path into the ‘variable value’ field. Say OK, and close out the ‘My Computer’ properties windows.
5) Create a file called ‘TNSNAMES.ORA’ in your instant client installation directory. In this file designate the connection parameters for your database. Mine looked something like this:
(ADDRESS = (PROTOCOL = TCP)(HOST = ip.add.re.ss)(PORT = 1521))
(SID = orcl)
(SERVER = DEDICATED)
replace the DB_ALIAS, ip.add.re.ss, port, and SID with your settings. If you don’t know what any of this means, good for you, you’ll live longer.
6) Reset your computer, fire up Toad, and then open a new connection. You should see DB_ALIAS as an option under the database field. Set your username and password, hit connect, and you should be in business.
I guess Toad looks in certain places for the drivers and configuration files it needs to connect to oracle. Setting the environmental variables tells Toad where to look. If you had a local install of Oracle I imagine you could fire up Toad and all the files it needed would already be in the right place, but you can follow these steps instead. Woot!