Tag Archives: SQL*Plus

EZCONNECT without a password

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

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
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: