Oracle: InstantClient, TNSPING, and PL/SQL Developer

Well, anyone that has tried to use tnsping with Oracle's InstantClient (http://www.oracle.com/technology/tech/oci/instantclient/index.html) was in for a big nasty surprise... it's not included. Not only is not included, you can't download a version that is compatible with InstantClient. I've been a fan of tnsping for some time. It has always been valuable as a quick way to test the "reachability" factor of a particular database. I now have to admit that it's not required since SQLPlus technically can perform this functionality. Well, habits die hard.

Goal: Get InstantClient working with tnsping and AllAroundAutomation's PL/SQL Developer (http://www.allroundautomations.nl/plsqldev.html).

Solution:

Disclaimer:

1. I don't want to tell you how long it took me to figure this all out, but it was way too long.
2. It's an unacceptable solution from a long-term maintenance, support standpoint.

Steps:

1. Download the appropriate InstantClient "base" (in other words, English-only or International).
2. Download the InstantClient SQL-Plus "add-on".
3. Extract both Zip files into the same directory [for this example, "c:\oracle\instantclient"].
4. At this point, we haven't done anything special.
5. Define an environment variable oracle_home that points to the location of your InstantClient directory (e.g.: "c:\oracle\instantclient").
6. Create a subdirectory called "network" (c:\oracle\instantclient\network).
7. In "network", create a subdirectory called "admin".
8. If desired, put your tnsnames.ora and sqlnet.ora in c:\oracle\instantclient\network\admin [technically, this isn't required but it follows Oracle's "standards"].
9. Define an environment variable tns_admin that points to the location of your tnsnames.ora (if you are using one).
10. In "network", create a subdirectory called "mesg".
11. From an Oracle 10.x Fat client installation, copy tnsus.msb into c:\oracle\instantclient\network\mesg. This is the file that tnsping uses to "lookup" the message text while running.
12. At this point, you should be able to fire up PL/SQL Dev and still have things work.
Okay, now the weird part starts.
13. Create a subdirectory called "bin" (c:\oracle\instantclient\bin), and yes, it must be called bin.
14. From an Oracle 10.x Fat client installation the following files into c:\oracle\instantclient\bin:
• heteroxa10.dll
• oci.dll
• ocijdbc10.dll
• ociw32.dll
• oraclient10.dll
• oracommon10.dll
• oracore10.dll
• orageneric10.dll
• orahasgen10.dll
• oraldapclnt10.dll
• oran10.dll
• orancds10.dll
• orancrypt10.dll
• oranhost10.dll
• oranl10.dll
• oranldap10.dll
• oranls10.dll
• orannzsbb10.dll
• oranro10.dll
• orantcp10.dll
• orantns10.dll
• oraocci10.dll
• oraociicus10.dll
• oraocr10.dll
• oraocrb10.dll
• oraplp10.dll
• orapls10.dll
• ORASLAX10.DLL
• orasnls10.dll
• orasql10.dll
• Orasqlplusic10.dll
• oraunls10.dll
• orauts.dll
• orauts_imp.dll
• oravsn10.dll
• oraxml10.dll
• sqora32.dll
• sqoras32.dll
• sqresja.dll
• sqresus.dll
• tnsping.exe
15. Now, copy all the files from c:\oracle\instantclient into c:\oracle\instantclient\bin except those that already exist.

At this point, InstantClient is "valid", PL/SQL Developer works [but it's actually using c:\oracle\instantclient\bin, and tnsping should work from c:\oracle\instantclient\bin. Talk about smells bad. So if you want tnsping for InstantClient, create this nightmare-ish drink from this recipe or complain to Oracle and ask for a InstantClient-compatible version of tnsping. Jason