Skip to Main Content

Oracle Database Discussions

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

ORA-12504 from sqlplus but the connection is still made

870204Jun 21 2011 — edited Jun 21 2011
Hi Oracle Guru's et al,
I am trying to figure out an error I get from SQLPLUS.

When I attempt to connect to my oracle instance with sqlplus it connects successfully and without error with the following commands:
"sqlplus" OR "sqlplus <username>" OR "sqlplus <username>@<SID>"

However when I try to do the same with the below commands I get the error "TNS:listener was not given the SERVICE_NAME IN connect_data " but the connection is still made:
"sqlplus <username>@<hostname>:<port>/<sid>" OR "sqlplus <username>@<IP Address>:<port>/<sid>"

I am looking at the syntax from the url below and also the command-line help: http://download.oracle.com/docs/cd/B19306_01/server.102/b14357/qstart.htm#sthref46

When I run the command "tnsping <sid>" I get a response showing what is in my tnsnames.ora file, with "OK (0 msec)" at the end. I can also connect successfully using the username, <IP or Hostname>, SID, port with SQL Developer with connection types: basic, TNS and a jdbc connection string works also.

I am using an IP address in the tnsnames.ora and listener.ora.

There is nothing special listed in my hosts file relating to this system (apart from 127.0.0.1 localhost) This is a windows environment.

Why do I get the error: "ORA-12504: TNS:listener was not given the SERVICE_NAME in CONNECT_DATA" when making a connection via sqlplus when the hostname or IP is included and why does it still allow me to connect?

(I am guessing the second part of my question is due to the fact that the other connection details "magically" make it work)
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Jul 19 2011
Added on Jun 21 2011
6 comments
6,245 views