Monday, March 14, 2011

Oracle Easy Connect - no password in the commandline

When using Oracle's Easy Connect feature, I (re)discovered some strange behaviour. The easy connect does not always translate the easy connect string to the right connect descriptor.

I set up Easy Connect by modifying my sqlnet.ora file on the client. Modifying the line with the directory_path in it should be enough:

  names.directory_path = (TNSNAMES,EZCONNECT)

After that, I tried to connect using easy connect:

  sqlplus myuser@myhost:1521/myservice

This returned an ORA-12541 Error: "TNS:listener does not currently know of service requested in connect descriptor". At first, I looked this up in the sqlnet.log file to see what he was trying to do:

  Fatal NI connect error 12514, connecting to:
   (DESCRIPTION=(CONNECT_DATA=(SERVICE_NAME=myhost)(CID=(PROGRAM=sqlplus@myclient)(HOST=myclient)(USER=myuser)))(ADDRESS=(PROTOCOL=TCP)(HOST=)(PORT=1521)))


So, it just messes up the entire connect string and tries to connect to a service called "myhost". This all disappears when you connect using a password on the commandline:

  sqlplus myuser/mypassword@myhost:1521/myservice

According to the documentation, this should not be a problem, but when searching for this on Oracle support, I came across Note 274757.1, which states that without specifying a password, you must enclose the connect descriptor in double quotes (and depending on the OS escaping these with \):

  sqlplus myuser@"myhost:1521/myservice"

And after that, SQL*Plus asks for the password as expected. In this case, I think the documentation could use an update, specifying this behaviour...

No comments:

Post a Comment