ORA-12504: TNS:listener was not given the SERVICE_NAME in CONNECT_DATA

Okay I think I have it working now. I guess the problem was the uppercase vs lowercase BORG vs borg stuff in the tnsnames and listener.ora files. Here is what I have in the end:

listener.ora

Code:

---

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = borg.ourdomain.com)(PORT = 1521))
    )
  )

---

tnsnames.ora

Code:

---

borg =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = borg.ourdomain.com)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = borg)
    )
  )

---

And then, as a normal user (not root or oracle user) I run the following command:

Code:

---

> sqlplus system

---

Then input my password for the user 'system' and then it successfully gives me the SQL prompt.

However, if I try this command:

Code:

---

> sqlplus system@borg

---

or

Code:

---

> sqlplus [email protected]

---

It asks me for the password and then gives the following error again:

Code:

---

ERROR:
ORA-12504: TNS:listener was not given the SERVICE_NAME in CONNECT_DATA

---

Got any clues why?

I struggled, and struggled, and struggled with this problem. I eventually figured out what was going on. I had installed both server and client on the same machine. This results in two Oracle directories - one for client and one for server. This also results in TWO tns_names directories. Likely the tns_names directory in your client folder will be EMPTY. Thus, any client you use (including java code) can't connect as there is no tnsnames.ora file to use. It won't use the server tnsnames.ora file - it will look for a tnsnames.ora file in the client directory instead and give up when it's not there. When you connect with SQLPLUS, it's bypassing the tnsnames.ora file.

You've got two choices to fix this problem. One is to simply copy the tnsnames.ora and listener.ora file from the server directory to the client directory. For example, on my system the directories are as follows:

C:\app\BioReg\product\11.2.0\dbhome_1\NETWORK\ADMIN
C:\app\BioReg\product\11.2.0\client_1\network\admin

The better solution is to define the Windows Environment variable TNS_NAMES and enter your server tns_names directory path. This way, you don't have to copy the tnsnames.ora and listener.ora file over to the client folder each time you make a change.