TNS:could not resolve the connect identifier specified – ORA 12154

I was trying to load some data and each time I ran the SQL script, I got the message “ORA 12154 TNS:could not resolve the connect identifier specified.” Since, I am not on the Oracle side but rather on the Hyperion side, some research regarding “TNS names” and the “ORA 12154” error helped me solve the problem. The reason I am entering this on my blog is because it might be helpful to folks who come across the same issue.

The above error can be seen for various reasons as per documents:

                a. Oracle net libraries not being able to get the TNS alias from “TNSNAMES.ORA” file.

                b. Something is wrong with the way the alias is created in the “TNSNAMES.ORA” file, such as   incorrect syntax.

                c. TNS alias could not be resolved into a connect adapter.

There are several things we need to check and make sure they are correct.

1. Verify that the TNSNAMES.ORA file has the "service name" and "alias" that are being used. In my case, the path to the file is \\SERVER\D$\oracle\database\product\11.2.0\client_1\network\admin

2. Take a look at a file SQLNET.ORA in \\SERVER\D$\oracle\database\product\11.2.0\client_1\network\admin folder and make sure that you have TNSNAMES in NAMES.DIRECTORY_PATH.

For example:

NAMES.DIRECTORY_PATH= (TNSNAMES, EZCONNECT)

3. Try and see if  you can log in to Oracle using SQL developer, SQL PLUS or any other tool that you use in your environment.

4. Ensure that the ENVIRONMENT VARIABLE "PATH" has the path for the TNSNAMES.ORA file specified.

If you are not sure how to check the ENVIRONMENT VARIABLE "PATH", - in Windows XP
a. Start -> My Computer -> Right Click -> Properties.
b. Click the ADVANCE tab and then ENVIRONMENT VARIABLES
c. Under "SYSTEM VARIABLES" you will see a bunch of "varialbes" and "values" They should be in alphabetical order. Key in "P" to get to the variables starting with "P"
d. When you get the "PATH" click the EDIT tab.
e. You will see a lot of paths here. The easiest way to view is to copy and paste it to a word processor such as Notepad or Textpad, whatever you prefer or have.
f. Check and see if the path to TNSNAMES.ORA is specified here.
g. Also ensure that you have the path for Oracle_Home specified.
h. One thing very tricky about the PATH that I have noticed is that even when you have the path specified, it does not work. In that case, if your variable path is towards the end of the path, try to move it towards the beginning. I am not sure if it is a glitch or if there is restriction in the number of letters, but I have noticed quite a few times when it worked by moving the path towards the beginning.
i. Another interesting and sometimes confusing issue is that sometimes we happen to have the Client installed more than once and the path points to the wrong one. This is also sometimes an issue. So make sure that you have the path of the right one.
j. Check the registry for Oracle_Home (the value). If you are not sure how to check it.
          a. Start command prompt. START -> RUN -> Type in "REGEDIT" and hit enter. You will see the REGISTRY EDITOR screen.
          b. On the left pane -> HKEY_LOCAL_MACHINE \SOFTWARE\ORACLE\Key_OraClient11g_home1. On the right pane, you will see the path. Ensure they are correct.
k. Check entry for TNS_ADMIN in the registry editor: HKEY_LOCAL_MACHINE \SOFTWARE\ORACLE. If it is there make sure it has the right path: ...client1\network\admin. If it is not there, create one.
i. If you think you have more than one instance of Oracle client, you can check if there are more in the registry editor: HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE\ALL_HOMES\HOME_COUNTER.

After ensuring all of the things specified above are correct try to run the SQL script, it should work unless there are some other issues with the system.