Oracle: Using SQLPLUS and SQLLDR on databases without TNSNAMES entry

There are circumstances when you would like to connect to databases with tools like SQLPLUS or SQLLDR which are not registerred in TNSNAMES.ora.

For example when you have no local admin rights to change the TNSNAMES.ora or you want to connect to databases you only rarely need, or just when you want to test a connection.

It works quite straight forward, you just have to put the same data as in the TNSNAMES entry into the connection string, although its a bit different in SQLLDR and SQLPLUS – that all was done and tested under Windows, but should be very similar on any other OS with an Oracle client:

For SQLPLUS, you can do like that (for  example, executing “myscript.sql” on SID “MYDBSID” on database host “myDBHost”):

sqlplus -L username/password@"(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=myDBHost)(PORT= 1521)))(CONNECT_DATA=(SERVER=DEDICATED)(SID=MYDBSID)))" "@myscript.sql"

For SQLLDR, it’s quite the same, but you have to escape all quotes and brackets, so it looks a bit more ugly.
Importing mytable.ldr to SID “MYDBSID” on database host “myDBHost”:


sqlldr userid=username/password@\"\(DESCRIPTION=\(ADDRESS_LIST=\(ADDRESS=\(PROTOCOL=TCP\)\(HOST=myDBHost\)\(PORT= 1521\)\)\)\(CONNECT_DATA=\(SERVER=DEDICATED\)\(SID=MYDBSID\)\)\)\" Control='mytable.ldr' Log='mytable.log'

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: