6 Replies Latest reply: Jun 21, 2011 2:46 AM by 870204 RSS

    ORA-12504 from sqlplus but the connection is still made

    870204
      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)
        • 1. Re: ORA-12504 from sqlplus but the connection is still made
          Girish Sharma
          Welcome to Oracle Forum..

          Please post following answers :

          1.Your Oracle database 4 digit version
          2.c:\>hostname
          3.Your tnsnames.ora
          4.Your listener.ora
          5.Copy and paste from command window to see how you are going to connect.

          Regards
          Girish Sharma
          • 2. Re: ORA-12504 from sqlplus but the connection is still made
            NikolayIvankin
            >
            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?
            Are you sure, that the connection is being established? Are you able to run any SQL query to any dynamic view?
            • 3. Re: ORA-12504 from sqlplus but the connection is still made
              870204
              Good point Nikolay, but even more basic I think... It seems to completely ignore the connection string, as if I had just typed "sqlplus".
              I missed something glaringly obvious, when I get the error, I am also asked for my username and password again....dur.

              If I provide the userpassword on the commandline sqlplus connects without error. ie: sqlplus user/userpass@<host>:1521/orcl
              Maybe.... it is something to do with the format of the connection string? it's directly pasted blow

              sqlplus typung@orcl - works (prompted for password, after password sql prompt can interact with db.)
              sqlplus typung@boxy3:1521/orcl - doesn't work (error, prompted for username and password, once logged in can interact with db)
              sqlplus typung/apassword@boxy3:1521/orcl - works (get to SQL prompt can interact with db)

              Anyway:
              1.Your Oracle database 4 digit version
              11.2.0.1

              2.c:\>hostname
              boxy3

              3.Your tnsnames.ora
              # tnsnames.ora Network Configuration File: C:\app\Administrator\product\11.2.0\dbhome_1\network\admin\tnsnames.ora
              # Generated by Oracle configuration tools.

              LISTENER_ORCL =
              (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.2.2)(PORT = 1521))


              ORACLR_CONNECTION_DATA =
              (DESCRIPTION =
              (ADDRESS_LIST =
              (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
              )
              (CONNECT_DATA =
              (SID = CLRExtProc)
              (PRESENTATION = RO)
              )
              )

              ORCL =
              (DESCRIPTION =
              (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.2.2)(PORT = 1521))
              (CONNECT_DATA =
              (SERVER = DEDICATED)
              (SERVICE_NAME = orcl)
              )
              )




              4.Your listener.ora
              # listener.ora Network Configuration File: C:\app\Administrator\product\11.2.0\dbhome_1\network\admin\listener.ora
              # Generated by Oracle configuration tools.

              SID_LIST_LISTENER =
              (SID_LIST =
              (SID_DESC =
              (SID_NAME = CLRExtProc)
              (ORACLE_HOME = C:\app\Administrator\product\11.2.0\dbhome_1)
              (PROGRAM = extproc)
              (ENVS = "EXTPROC_DLLS=ONLY:C:\app\Administrator\product\11.2.0\dbhome_1\bin\oraclr11.dll")
              )
              )

              LISTENER =
              (DESCRIPTION_LIST =
              (DESCRIPTION =
              (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
              (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.2.2)(PORT = 1521))
              )
              )

              ADR_BASE_LISTENER = C:\app\Administrator


              5.Copy and paste from command window to see how you are going to connect.
              C:\Users\Administrator>sqlplus typung@boxy3:1521/orcl

              SQL*Plus: Release 11.2.0.1.0 Production on Tue Jun 21 15:46:21 2011

              Copyright (c) 1982, 2010, Oracle. All rights reserved.

              ERROR:
              ORA-12504: TNS:listener was not given the SERVICE_NAME in CONNECT_DATA
              • 4. Re: ORA-12504 from sqlplus but the connection is still made
                NikolayIvankin
                try to set ORACLE_SID variable and connect with sqlplus / as sysdba
                • 5. Re: ORA-12504 from sqlplus but the connection is still made
                  Girish Sharma
                  No need to set oracle_sid because you are going to tell on which database you are going to connect. So just try with :
                  c:\> sqlplus scott/tiger@boxy3:1521/orcl

                  Its working fine at my end 10.2.0.1 on windows xp.

                  Regards
                  Girish Sharma
                  • 6. Re: ORA-12504 from sqlplus but the connection is still made
                    870204
                    This solved my problem:
                    [http://oradim.blogspot.com/2009/07/sqlplus-ezconnect-password-prompt-and.html]

                    The problem is the command-line arguments get mungled:

                    ie: sqlplus system@\"<HOST>:<PORT>/<SID>\" works.

                    who wudda thunk....

                    Thanks for the assistance.

                    Edited by: user5808686 on 21-Jun-2011 00:45