SQLDeveloper or TOAD

Judging by the title I suppose your expecting some exhaustive ( exhausting ?) comparison between the giants of the Oracle IDE world ( or if you’ve been here before, another ramble through the remnants of my sanity).

So, before I launch into my treatise on why SQL*Plus is better than both, it’s only fair that I share with you the fruits of my in depth, impartial research.

Only one test was required – put them into Googlefight.

In case your not aware of it, www.googlefight.com is the URL – simply put in two search terms and watch them fight it out !

For the record, “TOAD” beats “SQLDeveloper”, “SQLDeveloper” beats “Quest TOAD” and, most gratifyingly, “SQL*Plus” knocks seven bells out of the pair of them !

That’s the pretence of impartiality out of the way, now for the bias and invective. Are you sitting comfortably ?

When I’m writing anything more complex than a simple, one time SQL statement, I’ll use Textpad. Syntax highlighting, no annoying code auto-completion popping up and inserting an option or keyword I don’t want, and when I want to compile/test, I just shell out and run SQL*Plus. Simple.

If I want to do any admin on the database, I’ll always use the command line – whether it’s running object creation scripts or general housekeeping tasks, I want to know that the database is doing exactly what I’ve asked it to do and the IDE isn’t trying to do anything clever behind my back.

One major reason for my reluctance to use an IDE for DDL is the propensity for it to crash partway through the statement, leaving the target object locked by a zombie session that you then have to hunt down and kill in the OS. Toad was particularly renowned for this at one point.

Another amusing “quirk” of both tools is that they tend to let you get away with some funky stuff where escaped quotes inside strings are concerned. Whereas SQL*Plus will give you an ORA-01756 “Quoted string not properly terminated”, the IDEs will just chug on. The results are likely to be other than what was intended.

In SQLDeveloper especially, when you do get an error message, it seems to be a bit hazy on the line number. Sometimes it’s a bit reluctant to divulge the actual error message.

At this point I usually just cut and paste the offending statement into SQL*Plus and magically…it still doesn’t run. However, I do get a better idea of what’s going on and where.

I do use an IDE to browse database program units and other schema objects, and maybe take advantage of the explain plan or session monitor features from time to time.

The one utility I did find invaluable at some point is TOAD’s schema comparison tool, so much help when trying to synchronize code between environments without being able to just replicate the database.

Having said all of that, I must confess a preference for SQLDeveloper over it’s amphibian rival ( what is it about animals and software – toads, penguins, koalas – it’s a veritable menagerie).

OK, it’s not a smooth as Toad ( a by-product of being a Java Application), but it has a couple of things going for it that really appeal.

First, it’s cheap ( i.e. free). In the current climate, it’s worth considering that, if your shelling out tens of thousands of pounds for any software site license, that more or less equates to someone’s job.

Secondly, it’s easily extensible. You can write your own reports and save them for easy reference. More impressively, you can add your own tabs to an object view window.

Also, I’ve now found the setting that turns of the automatic code completion feature unless I specifically invoke it.

I’ve already posted about creating a report in SQLDeveloper and I’ll probably write something on adding a tab in the future.

In the meantime, hello Textpad, my old friend.