Tuesday, November 3, 2015

Remote database from command line

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: