Oracle: Connect to DB using Easy Connect

Assuming you have no privileges to edit the tnsnames.ora on your client, but have the need to connect via sqlplus to a certain DB that’s not yet included in tnsnames.ora. What can you do?!

Since 10g Oracle gives you the possibility to put all connect-stuff into one single command-line. No more hassle with finding and editing the (right) tnsnames.ora. This is called “Easy Connect” and it goes like this:

sqlplus myuser/mypassword@dbserver:port/db_service_name

You could even omit the portnumber if it’s the default “1521”.

But there is one catch. If, for apparent reason, you don’t want to read your password on the shell in cleartext and try to connect without password, you’d be faced with something like this:

[oracle@server1 ~]$ sqlplus myuser@dbserver.mydomain.com:1521/corpdb.mydomain.com

SQL*Plus: Release 11.2.0.2.0 Production on Mon Apr 23 19:25:29 2012

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

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

Enter user-name:

The errormessage is a little misleading. The reason for this error is, that sqlplus seems to identify the password by the next leading slash “/” after the username. But here this slash separates the service-name from the hoststring. For sqlplus we haven’t provided a connect-string – hence the: “TNS:listener was not given the SERVICE_NAME in CONNECT_DATA”.

So if we quote our connect-string all is fine:

[oracle@server1 ~]$ sqlplus myuser@\"dbserver.mydomain.com:1521/corpdb.mydomain.com\"

SQL*Plus: Release 11.2.0.2.0 Production on Mon Apr 23 19:32:15 2012

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

Enter password:

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> 

As I use the bash-shell here, I have to escape my quotes with backslashes.

This Easy Connect can also be used within sqlplus:

[oracle@server1~]$ sqlplus /nolog

SQL*Plus: Release 11.2.0.2.0 Production on Mon Apr 23 19:43:28 2012

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

SQL> connect myuser@"dbserver.mydomain.com:1521/corpdb.mydomain.com"
Enter password:
Connected.
SQL>

And here we don’t need to escape the double-quotes.

A prerequisite for using Easy Connect is, that it is configured as an allowed connection method in the sqlnet.ora – if you use the Oracle client:

NAMES.DIRECTORY_PATH= (TNSNAMES, EZCONNECT)

Otherwise you would get this error:

ERROR:
ORA-12162: TNS:net service name is incorrectly specified

This neat EasyConnect-Method can also be used along with database-links:

CREATE DATABASE LINK linkname CONNECT TO username IDENTIFIED BY password USING 'servername:port/servicename';