Thursday, February 26, 2009

SQL*Plus connection to remote Oracle database

SQL*Plus is a very handy command line tool when you want to quickly access an Oracle database or execute SQL statements from scripts, e.g. BASH scripts. However, getting it to connect to a remote Oracle database server in a script proved to be quite a challenge. This blog documents how to write the connection string in various cases.

Local database


Connecting to a local database is trivial, just use:

$ sqlplus dbUser/dbPassword@dbSid

Remote database from command line


Here's the nasty syntax for connecting to a remote database using its SID:

$ sqlplus dbUser/dbPassword@'(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=remoteServer)(PORT=1521)))(CONNECT_DATA=(SID=dbSid)))'

You can also connect using a SERVICE_NAME instead of SID.
Note that the single-quotes are needed to preserve the brackets.

Remote database from a BASH script


I needed to write a script that called SQL*Plus several times. In order to make it maintainable, the username, password and the part after the @ were specified as environment variables. Unfortunately, there did not seem to be any obvious way of setting the value of the environment variable so that the single-quotes were properly sent to the sqlplus command at run time. I had to explicitly escape each bracket with a backslash, like so:

DB_CONNECTION=\(DESCRIPTION=\(ADDRESS_LIST=\(ADDRESS=\(PROTOCOL=TCP\)\(HOST=remoteServer\)\(PORT=1521\)\)\)\(CONNECT_DATA=\(SID=dbSid\)\)\)

Because this script was part of a Java project, I subsequently moved this value to a Java properties file. This means every backslash had to be escaped with another backslash to prevent Java from interpreting it. The entry in the Java properties file:

db.connection = \\(DESCRIPTION=\\(ADDRESS_LIST=\\(ADDRESS=\\(PROTOCOL=TCP\\)\\(HOST=remoteServer\\)\\(PORT=1521\\)\\)\\)\\(CONNECT_DATA=\\(SID=dbSid\\)\\)\\)

(I know I could have just used JDBC, but there was a good reason why I had to use SQL*Plus for this Java project.)


Define a local service name to a remote server


An alternative to using the big ugly connection string is to define a local service name in the local tnsnames.ora that references the remote instance. Add the following:

DB_LOCAL_NAME =
(DESCRIPTION =
(ADDRESS = (PROTOCOL= TCP)(Host= remoteServer)(Port= 1521))
(CONNECT_DATA = (SID = dbSid))
)

to

${ORACLE_HOME}/server/network/admin/tnsnames.ora

Connecting to the remote database is simply:

$ sqlplus dbUser/dbPassword@DB_LOCAL_NAME

Of course, this solution has a few drawbacks (enough to dissuade me from using it).
- Need write permission to the tnsnames.ora.
- The application relies of custom entries in tnsnames.ora, which must be maintained together with the application code.
- Every environment (e.g. development, staging, UAT, production) in which the application is deployed to will need to have its tnsnames.ora modified and maintained.


21 comments:

  1. Good job! It works!
    Thank you!

    ReplyDelete
  2. Wow. Never thought when I google searched a similar phrase someone would have posted an exact tutorial for how to do this. If it were all this easy I wouldn't choose to pay for remote connection software!

    ReplyDelete
  3. Thanks, Keep posting ...;)

    ReplyDelete
  4. A useful article.
    You may just add that if you have only sqlplus client and you do not have any ORACLE_HOME you may still define local service names in $HOME/.tnsnames.ora

    ReplyDelete
  5. You have saved me from almost certain termination with this. I salute you! Cheers!

    ReplyDelete
  6. Thanks, I have a question though - what if your DB instance allows you to connect over LDAP authentication only. Will be able to use the sqlplus utility in that case ?
    If yes, what is the syntax to connect to a LDAP service. Consider the ldap.ora file has been configured for an OID authentication.

    ReplyDelete
    Replies
    1. Sorry I haven't tried this myself, but I suppose it can be done.

      Delete
  7. I need to run update query on multiple remote SQL servers and i will connect using SQLplus to them. The Server SID's, PortNumbers, Usernames, Passwords and Servers Host ID's will be passed as arguments to a script during runtime. Is there a way out how can i update different SQL DB's on different machines using sqlplus...........???
    Waiting to hear from you...

    ReplyDelete
    Replies
    1. Isn't it just a matter of using the passed in arguments to construct the command? Like so:

      DB_CONNECTION=\(DESCRIPTION=\(ADDRESS_LIST=\(ADDRESS=\(PROTOCOL=TCP\)\(HOST=${host}\)\(PORT=${port}\)\)\)\(CONNECT_DATA=\(SID=${sid}\)\)\)

      sqlplus ${dbUser}/${dbPassword}@$DB_CONNECTION

      Assuming you've extracted the command arguments into dbUser, dbPassword, host, port and sid variables.

      Delete
  8. Thats a very nice post, Alexander.
    I just wanted to know if its possible to avoid writing the password in the script. Like we do in SSH setups, can we somehow encode the password and use the encoded password with sqlplus?

    ReplyDelete
    Replies
    1. Create an Oracle user that is IDENTIFIED EXTERNALLY and rely on the host OS authentication.

      Delete
  9. very useful, thanks :)

    ReplyDelete
  10. Thank you. very useful

    ReplyDelete
  11. jitender -thanks ..nice post

    ReplyDelete