Tuesday, November 27, 2007

Secret Squirrel on the move

I've finished, for now at least, my voyage into Teradata, and am half back in the Oracle world.
I say half because I am also using SQL Server (for the first time).

I'd recommend any Oracle developer try to familiarise themselves with some of Oracle's ANSI-standard constructs, like COALESCE instead of NVL, CASE instead of DECODE and the ANSI OUTER JOIN syntax. It gives you a bit of a headstart when attacking these alien worlds. Despite Larry's intentions, at some point you WILL come across a non-Oracle database.
Even ANSI isn't perfect though. Simply updating one table from another is a major switch between Oracle and SQL Server and there's no universal syntax for it. Plus I have to keep reminding myself that Oracle is practically unique in not differentiating nulls and empty strings. Even EnterpriseDB doesn't go down that prickly path. Also collation and ordering has been an interesting challenge.

I'll also recommend Secret Squirrel. Okay it is actually SQuirreL SQL Client rather than the undercover cartoon rodent, but it was a secret to me. It is a Java-based cross-database query tool. While I've only used it for Oracle and SQL Server, it looks like it will cope with pretty much anything you'll come across, as long as you can find a JDBC driver for it. I should have tried it for Teradata.

Squirrel is more compact (at about 25Mb) than SQL Developer, and also only needs the Java Runtime, not the full JDK. It is also better at handling multiple sessions against the same DB server. [Note: I'm trying to use the term 'DB server' because 'database', in SQL Server and Teradata, is closer to what Oracle bods consider a schema/user.]

Squirrel isn't an IDE to the extent SQL Developer is, but then I never really used that for coding PL/SQL anyway, preferring to stick to SQL*Plus and my favorite PSPad editor (which I've got working under Wine in Ubuntu). It also doesn't have the level of Oracle specific support (eg built-in reports). The Explain Plan seems basic (not using some of the new columns), but I prefer DBMS_XPLAN.DISPLAY anyway.

It is also cluttered. That may be because I'm still working out what I use and don't use, and what I can turn off or hide.

Finally, as you'd guess from the SourceForge location, it is Open Source (Lesser GPL if that is important to you), and seems pretty active. The latest version has a nice built-in for copying data from one database to another (like the old SQL*Plus COPY).


Noons said...

Hope squirrel has a lesser tendency to modify stats on db objects than SQL Developer does...

The main reason why it's now banned from any of our production dbs: last thing I need is a developer inadvertently and unknowingly invalidating all the execution plans in memory for a high volatility table!

BTW: lunch some time this week? Check with Master Peter as well?

Lord Burner said...

Hi Gary,
Have a look at SQLTools (www.sqltools.net) It's a lighweight TOAD-like IDE, but less functional than TOAD and much faster.

squirreldev said...

When you say modify stats, are you refrerring to

analyze table mytable compute statistics;

SQuirreL doesn't do this. But perhaps you were referring to something else?

Rob Manning

Gary Myers said...

I have had a look at SQL Tools, but it doesn't have SQL Server connectivity. So, for my current role, Squirrel is a better fit.
I'll keep SQL Tools in mind if I am ever back on a pure Oracle project.