After encountering this little annoyance for the nth time, I thought I should write it up once and for all, so it is committed to my electronic memory.
The EZCONNECT naming method is a handy way of connecting to an Oracle database by its service name, which avoids the need for aliases in the TNSNAMES file, or lengthy SQL*Net connection strings :
SQL> connect user/password@hostname:port/service_name Connected.
However, one would expect that if you don’t want to specify the password on the command line, then simply omit the password and await the prompt. But…
SQL> connect user@hostname:port/service_name ERROR: ORA-12504: TNS:listener was not given the SERVICE_NAME in CONNECT_DATA
Without the password present, the command interpreter parses the string differently, and (according to Oracle Metalink note 267427.1) treats the service_name as the password and the hostname as the service_name. Basically it’s not handling the syntax very well.
The solution is to quote the part of the connection string after the @ symbol. The actual quoting required may vary by environment, but something along the following lines works in Windows.
SQL> connect user@'hostname:port/service_name' Enter password: Connected.