What's a quality development environment for writing Oracle SQL?

Question

The project I'm working on has an Oracle backend, and we use SQL Navigator 5.5 for SQL development and it really sucks: the intellisense doesn't work for queries with more than one table and you can't browse the schema and write a query on the screen at the same time. Not fun.

I also tried using Oracle SQL Developer. That's not much of an improvement either - the graphics on the query results window get all fudged up and have to be repainted frequently by clicking around the screen.

The extent to which I'm writing SQL is pretty basic: a procedure here and there, mostly queries. Can someone recommend a decent tool, preferably one with a low cost?

Answer 1

There are several tools out there you could give a try. TOAD or DbVisualizer are examples for tools which could make you happier than SQLDeveloper. Altough, most of the available tools are commercial.

Answer 2

I'm pretty sure that TOAD is the most used tool. I don't recall how much it costs now days, but I'm generally very satisfied with it even with a slightly older version.

Answer 3

I used CAST for 2004-6, and it's quite good. One of the principal reasons for using it was that it supports locking of PL/SQL packages when editing them. Now I use jEdit for almost all developments, and Oracle SQL Developer for easy lookup of table properties and such.

Answer 4

There is a freeware version of TOAD, though it's not as fully featured as the paid ones. You can find it at:

http://www.toadsoft.com/

Answer 5

There is fairly decent support in Emacs using sql-mode and plsql-mode. I wouldn't recommend it unless you are an Emacs user though.

Failing that Toad is fairly good, although it fairly heavy weight.

Answer 6

If you're looking for an open source solution SQuirreL SQL Client is pretty good. It isn't Oracle specific though. It works with any jdbc client.

Answer 7

You may want to check out PL/SQL Developer (http://www.allroundautomations.com/plsqldev.html). It does more than just PL/SQL. Cost is pretty reasonable. On Linux try Tora It is suppose to be a replacement for Toad. Have used to other 2 personally but not the latter.

Answer 8

Another open source tool which works really well is SQL Workbench (http://sql-workbench.net/). Works with all JDBC clients, but we've had alot of success with Oracle. Also includes some scripting support which can be helpful for importing/exporting large amounts of data.

Answer 9

All IDE:s for Oracle DB are more or less bad, TOAD is the best but still nothing comapared to real IDE like Visual Studio or Eclipse.

Main problem with TOAD is the default texteditor. Find is nightmare and you can't change shortcuts and auto alignment uses spaces even if you configure it to use tabs and other annoying features. Also cancelling queries or making query to disconnected session might crash it. Also the intellisense is kinda slow imo but it works which is better than what others offer.

Answer 10

I personally use PL/SQL Developer to develop and Spotlight on Oracle to profile.

Answer 11

Two more options (just for completeness):

• Hora

• Aqua Data Studio

Answer 12

SQL*Plus: it's free with the database, and always guaranteed to be there - and there's nothing you can't do with it :)