Oracle has now been the dominant database for quite some time now. It is indeed a powerful database, with a plethora of high end features. Why is it then that the primary interface for the database administrator is a poor command line tool, which has remained essentially unchanged for more than 10 years?
Yes, I'm talking about good old SQL*Plus. Despite being used by a generation of DB administrators, it lacks simple functionality which would greatly ease the administrator’s life.
In particular:
• Line editing capabilities. I can't tell you how many times I've typed a long SQL statement just to realize that I had typed "selct" way back at the beginning. Sqlplus does have (weak) sed-like editing capabilities, and the ability to edit the previously run command in your choice of editor, but that's only AFTER you've executed your faulty command. From what I've seen, "backspace" seems to be the editing method of choice.
• History. Sqlplus only stores the last command you have run. But when I'm looking around in several tables, I need to go back and recover previous commands. Almost invariably, I end up opening another window just so I can copy the commands that I need to run repeatedly.
• Table/Field name completion. Every modern IDE has the useful feature of allowing code completion -- you type part of a command, and the IDE can suggest what you want to enter.
Why can't Sqlplus do the same?
The really embarrassing thing for Oracle is that these kinds of features have been around on open source command line tools for years, thanks in large part to the GNU readline project. Take a look at the free (for non-commercial use) database MySQL; their command line tool has had these for years.
I have a theory that Oracle intentionally has not provided a better interface with the Oracle database because they want to encourage third party developers. In particular, Quest Software's Toad is a very nice graphical front end for Oracle databases. I used to work on a project where they bought this tool for the developers. But it seemed overkill (and expensive) for a user that just wanted to run some SQL commands.
So what's my preferred way of talking to Oracle? I'm a big fan of the open source project SQuirreL SQL. Here's some of the great features it gives you:
• It's written in Java, so the same download works on a variety of systems.
• The graphical interface is nice. It uses a MDI (multiple document interface) so you can have several connections going at once.
• It uses JDBC, so the same application can talk to a wide variety of databases -- not just Oracle!
• Table name and field name completion is supported. Just hit CTRL-space and it will show you what tables/fields are available.
• You can easily generate table creation and data creation scripts.
• Table relation graphs can easily be generated.
• SQuirreL uses a plugin architecture so other functionality can be added as desired (DB specific commands, SQL validator, syntax highlighting)
The downside? Since SQuirreL is database agnostic, it doesn't handle non-standard database features very well. In particular, the handling of stored procedures is weak. For a DB expert who is working with stored procedures, this is a compelling reason to go with Toad.
But both Toad and SQuirreL SQL are GUI applications. Why hasn't someone come up with a good command line SQL utility?
Comments
I used to use TOAD, but got fed up with what I consider are a lot of quirks with it (and the fact that it must be licensed per developer and is very expensive). We found TORA a few years ago and I think it is fantastic. It is open source and written in C - thus very fast. It handles pretty much everything as well as or better than older versions of TOAD, at least.
Unfortunately, Quest went and hired the main developer for TORA a year or two ago in an obvious attempt to stifle an OSS product that had already surpassed their very expensive offering. After that, development has slowed to a crawl, but I still wouldn't consider using TOAD instead of TORA even if TOAD was free.
Downside for many/most is that it is most suited for Linux and similar OS's. It is said to be possible to run on OSX which is what I use these days, but for me it is just easier to run it on one of our Linux servers and present it locally on my Mac in X windows. The only annoying thing - and this can be very annoying at times - is that copy/paste into TORA is problematic. I think this is due to the fact that TORA uses the QT library more than anything else.
Nevertheless, highly recommended.
I stumbled accross this page today, which has some promising command line SQL*Plus replacement open source projects. In particular:
I'm glad to know that some people out there are addressing the problem!
Rick, I completely agree about SQLPlus. On the bright side, Squirrel is an excellent tool for your average application developer. I've used it a lot lately and I'm really excited because I can finally get comfortable with an sql editor and continue to use it with any DBMS. As long as you have the jdbc driver for the DBMS you're using, you're set!
Rick, try using SQL*Plus Worksheet (comes with the standard installation of Oracle) - it works as a text editor and saves your command history. For DBA work, the Oracle Enterprise Manager console (also comes standard) is a GUI interface with all kinds of management and performance tools.
Post new comment