Friday, November 26, 2010

PL/SQL Developer ORA-12154

ORA-12154:

TNS:could not resolve the connect identifier specified

There are many reasons you may come across with ORA-12154 error while connecting to Oracle database. Actually ORA-12154 is quite common error and you can google for common solutions. Now, I want to give solution to a weird one which you may hardly find otherwise. I experienced this on Windows 64-bit platform with ODAC 10.2.0.2.21 client installed while connecting to Oracle database from PL/SQL Developer.
First of all, check if tnsping works. Open command window type tnsping SERVICE_NAME. Here SERVICE_NAME is the oracle database you want to connect to and it must already exist in tnsnames.ora file where your client product is installed. If tnsping works it could resolve the alias as shown below.
If tnsping works and still PL/SQL Developer complains about ORA-12154 then check where PL/SQL Developer program is installed. If it is located in an address which contains brackets this is the problem. So uninstall and reinstall it in a location where location address doesn't contain brackets. This is weird! In my case it was C:\Program Files (x86)\PLSQL Developer so worked after i reinstalled in C:\Program Files\PLSQL Developer. I did try a lot before found this bug.
If tnsping does't work you may have problem with tnsnames.ora. Maybe file is inaccessible or is not in correct location or its content's syntax is incorrect or something else. I didn't provide detailed solution for this because there are lot of information about this on the internet.
Hope this helps.