Okay, enough with the melodrama. There are still many people who use SQL*Plus as a regular interface to Oracle. Some from choice. Others are constrained by the lack of a fancy-pants X-Windows interface on their server, or by the limits of SSH. [Yes, I know you can tunnel X-windows over SSH, but how well that works depends on the network bandwidth.] If you're never in the position of being 'stuck' with SQL*Plus, you can stop reading now.
In the shadows, there is something between SQL*Plus and a GUI. I'm not talking the replacements like pysql, or the handy rlwrap. I'm talking about the venerable enemy of vi, the emacs editor and it's sql-oracle mode.
When I first moved to Oracle from Ingres, I was a tad frustrated by the SQL*Plus command line. Ingres' isql was leaps and bounds beyond it from the point of view of interactive use. I was in a terminal-based VAX environment (no pretty beige PCs back then) and VAX had a beautiful editor called EVE. Using EVE I managed to hodge-podge an environment where the editor spawned a sqlplus sub-process and I could feed commands from the editor to sqlplus and get the results back in that editor. Yep, a command history and a results history and I could move horizontally across wide results sets still with their columns without any of that ugly wrapping.
EMACS has that all built in with a special mode called sql-oracle.
In the first screen shot, you see a Putty session from my Windows EeePC to my database server (actually just an Ubuntu VM running on the same machine, with XE installed). I've started up emacs on the server session from the command line. Emacs isn't part of the default Ubuntu install, but it should be available in most linux repositories or for other *nx systems. It's available for Windows too, but if you've got Windows then you're not restricted to this semi-GUI.
From emacs, type Esc then X (the Emacs "Meta" command sequence) and then enter sql-oracle. You get prompted for the username, password and database and then, Ta Da, you have sqlplus in the editor. You can set up some of this as defaults in the .emacs initialization file.
That gives you the nasty split screen in image 2. Ctrl-X followed by 1 will resolve that. Next you want to turn off the word-wrap with Esc, X then the command toggle-truncate.
The third screen shot shows a small query. I've set my SQL*Plus linesize to a large value. It buggers up DESC unfortunately, but you can't have everything. I can then query a table and see it all properly laid out. I can use ALT-arrow to move forward/back by words to navigate around the result set (or just arrow keys to move column by column)..
For single line SQLs, you can just navigate back up to the line and hit return to re-execute it. For multi-line SQLs, you need to get familiar with killing and yanking (cut and paste in emacs) rectangles, or user Ctrl-J at the end of lines rather than return. [It's called sql-accumulate-and-indent. No-one said emacs was friendly.]
You can find more on Emacs (and the various sql modes for different databases) on the EmacsWiki.
4 comments:
It's odd how, once you are used to a none-GUI, you can be so much more productive in one than sometimes you can be in an all-singing, windowing, click-driven interface. And, as you say Gary, sometimes the GUI is not an option.
That split-screen image took me back to my days with VAX/PDP11 when VT340s (or was it VT420s) and you could have split screen, one logged into your local dev systme and the other to your remote "live" system and you could release small bits of code with cut n' paste!
Nice! :)
I'm using sqlplus with very wide linesize as well.
How I've addressed the DESC issue is that I have a @desc.sql script in my SQLPATH, which does this:
set linesize 80
describe &1
set linesize 999
I have been known to quote that "Real men only need a command line and an editor, not this pointy-clicky rubbish that you young people use."
I shall henceforth follow that up with "As long as that editor isn't Emacs". Because as we all know Vim is the one true editor ;-)
If you are in the habit of a non-GUI, you can easily get comfortable with it, with your personal library of scripts (and Tanel's DESC is a good example).
I did some cut-n-paste jobs with those VT terminals too. I'm glad to say not any more.
I use vi(m) for minor edits, but tend to do any REAL editing in PSPad on Windows.
Post a Comment