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:
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:
(ADDRESS = (PROTOCOL= TCP)(Host= remoteServer)(Port= 1521))
(CONNECT_DATA = (SID = dbSid))
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.