Monday, November 27, 2017

rlwrap and sqlplus

Posted on 
As you know I'm a fan of the CLI in many (but not all) instances, especially ones who are engineered to work well for CLI'ers. I prefer mysql over Oracle and sqlplus over the heavy handed SQL Developer. The SQL Developer weighs in at over 181 Meg and while it has a nice GUI and extensive set of trees and widgets to explore every innard of Oracle's SQL implementation it's slow, slow, slow to load and run and ended up taking > 250 Meg of my memory when running. Suffice it say it was not very workable and definitely overkill for the simple selects and the like that I wanted to do.

Oracle's sqlplus weighs in at 700K or so, is light and quick to load and quick to execute commands. However it has a horrid CLI interface, not history, no re-editing of previous commands, etc. I'm surprised that backspace works honestly. However I could not continue to have SQL Developer bogging down my system and get any meaningful work done. My problems were twofold: 1) how to connect to the database (Oracle makes this so hard) and 2) how to improve on sqlplus' horrid CLI.

We actually have sqlplus at /data/tools/OraInstantClient_10_2 but when I run it it prompts for username and password then complains "ORA-12162: TNS:net service name is incorrectly specified". Trying sqlplus /@ was no better. I have no clue what's defined in tsnnames.ora on the server and can't even log into it to check if there's one at all. I've always detested Oracles tsnnames.ora approach anyway. Finally I figured out the following will work:

$ <path_to>/sqlplus -L <username>/<password>@<server>/<sid>
$ # or
$ /data/tools/OraInstantClient_10_2/sqlplus qcread/******@pdbgen02/pettd1p

However it still has a crummy CLI so it's rlwrap to the rescue! rlwrap is a little program that wraps a CLI with ReadLine giving you most of ReadLine's functionality. So now I have a simple alias of:

alias sqlplus='rlwrap /data/tools/OraInstantClient_10_2/sqlplus -L <username>/<password>@<server>/<sid>'

No comments:

Post a Comment

Posted on  November 23, 2005 Continued to help several users with connecting to the new Controller Clearquest Database for Vinh...