Wednesday, 19 March 2008

Taking the pain away from SQL*Plus in 11g

Before the Oracle 11g RDBMS release, I noticed that SQL*Plus users on Windows were divided into 2 camps; the users who wanted easy copy & paste, and the users who wanted the ability to scroll through previous commands.

(actually there is a 3rd camp, users who don't use SQL*Plus ;)

If like myself you lay in the former camp you used the Windows version of SQL*Plus – sqlplusw.exe. The Windows version happily supports Windows copy and paste but had no ability to scroll back through previous entered commands. To me that's no problem because I'm always working with my text editor of choice Ultraedit, copying backwards and forwards between the two, and not interested in what I last entered because it's in the text editor.

If you were in the later camp, the good old Windows Command Prompt (a'la DOS) version sqlplus.exe was great for you. Thanks to the up and down arrows, you could easily scroll through previous commands and even modify previous commands without having to resort to the SQL*Plus command line editing mode which has its roots in Medieval Computing. However if you wanted to copy and paste this introduced the horror of the Mark-Copy-and-Paste interface in the Windows Command Prompt which is just a pain in the b*tt to use. It seems Microsoft really doesn't want us to resort to good old DOS!

Within the 11g release of the RDBMS, Oracle (much to their wisdom ;P) has dropped the sqlplusw.exe Windows version and now suggests we either use the command prompt version or SQL Developer. I'm a big fan of SQL Developer (did somebody say free?!), but I find its SQL Worksheet clumsy and it's not my tool of choice for running scripts. So what to do with the SQL*Plus command line version? How can you use it with the beauty of both a useable copy and paste and historic commands?

One alternative is Console: "Console is a Windows console window enhancement. Console features include: multiple tabs, text editor-like text selection, different background types, alpha and color-key transparency, configurable font, different window styles".

Note my emphasis on "text editor-like text selection". Yeehaw! We now have copy and paste. Admittedly and understandable it doesn't use Ctrl-C and Ctrl-P as they have a different meaning in a normal DOS window, but rather Ctrl-Insert and Shift-Insert to copy and paste. In addition to mark text to copy, you hold down the Left-Shift key while dragging the mouse with the left mouse button held down. It takes a little getting used to but is better than the alternative.

Console is released under the GNU GPL making it free to use. Small note that the current release 2 build 138 is a beta release, so be wary that your mileage may vary. Well done to Marko Bozikovic for this useful little utility.


yas said...

Chris, I have never had a problem with sqlplus.exe in Windows. You can get copy-paste by setting an option in the command prompt. Right click on the title bar, select "properties", go to "options" and enable "quick edit mode" and "insert mode".
Now you can select text, right click to copy it, another right click to paste it.

Laurent Schneider said...

there is a big issue Oracle apparently forget is that sqlplusw could be used to run scripts.

Sqlplus.exe is in DOS mode, so if you speak chinese, portuguese or korean, you will probably have character sets issues. Does Oracle care about something else than US7ASCII ?

Gary Myers said...

cmd style have another option for Cut/paste. Right-click on the windows title bar, and go to Properties-Options and set QuickEdit mode.
Then you can select with the mouse (which will auto-copy) and paste with a right-click..

Chris Muir said...

@Yas & @Gary: hohum, here was my diligent search through the Vista helps files to ensure there wasn't some lurking feature which turned up little. And apparently a setting has been hiding under my nose all this time. Mind you it is mostly mouse bound which isn't ideal.

Needless to say, Console does come with a whole bunch of other features that exceed that of the standard command prompt that will prove useful to the budding command line guru.

@Laurent, that's an interesting point about the character sets. Is that another reason why Microsoft pushed us away from good ol' DOS?

John Stegeman said...

Hi Chris,

Nice to see someone else uses Console. I've been using it instead of the plain-old command prompt for a few years now. I do have to admit that I was one of the "I prefer DOS SQL*Plus to Windows SQL*Plus" in the first place, not because of the command history, but because I was so used to using SQL*Plus in a unix shell prompt. The Windows one always felt out-of place to me.

SQL*Developer is nice, but if I need to do something quick and don't want to wait the 20 seconds for SQLDev to start - SQL*Plus in a console window for me, please.

In case it helps someone, here's the command that I use in the shortcut to start up SQL*Plus in console from an icon in my quicklaunch:

"C:\Program Files\Console\console.exe" -c "/k C:\o\db111\BIN\sqlplus.exe /nolog"

Best regards

Chris Muir said...

@John, thanks :)

You know I nearly think we should start a petition for Oracle to bring back the sqlplusw.exe version, but then I think haven't I better things to do with my time?

Interesting fact about SQL Dev today. I'm currently running Vista and VMWare XP Pro virtual machine. SQL Dev loads as quickly in the virtual machine as it does natively on Vista. It's amazing how optimised the VMWare code is!

I mainly use SQL Dev for looking at data in the grid view, and as an easy point and click for inspecting the schema. Meanwhile all my coding and script files are still under my favourite editor Ultraedit. It's this fact alone why I found it so easy to move from Toad to SQL Dev, because I really don't use many of the features. And I'm a big fan of free.

OraTek said...

I agree totally. It is an absolutely dumb decision - probably made by development focused folks rather than DBA folks. As mentioned, the windows command prompt is using a completely different codepage to the rest of the OS.

See this relevant section from the NLS_LANG FAQ:
"The correct NLS_LANG for Windows Command Line Operations"

There will absolutely be character set issues, and sqlplus is the only program to use in a .bat or .cmd file.

Oracle advocates a 'migration path' of SQL Developer or APEX. That's not a migration path unless you are a developer that uses sqlplus,

Anonymous said...

The way I do it is by copying from a text editor the usual way (Ctrl C) and pasting the script on Sql plus by a right click.