Wednesday 22 September 2010

SQL Developer gripes - Part II

Last week I commented on some issues I have with SQL Developer, from which a number of people commented and e-mailed me about.

Stew in particular commented on his usage of PL/SQL Developer (aka PSD) and asked the question:

  why would you move away from using PL/SQL Developer?

So I thought it would be polite to respond with a fresh post.

I think the best way for me to respond to that question is to highlight the most pertinent part of that statement - "move away"

To add some more context:

  • I learned Oracle using SQL*Plus. I like using it, I still do for some tasks.
  • I started using PSD when we (myself and the fellow that sent the original e-mail that spurred this discussion) pushed the company we were working for to supply a GUI tool. We evaluated a few, and PSD fitted the bill. It served me well, and the company managed the licences.
  • In mid 2005 I started with another company doing mainly Oracle Forms programming - so I was essentially forced to stop using PSD as I no longer had a licence, and I didn't really need a GUI at the new job.
  • When SQL Developer was first released, I did download and use it occasionally, but since I learned Oracle using SQL*Plus and a fancy text editor, I managed to retain productivity. I have a suit of scripts that displays the same information I get out of the GUI tool and have them ready to fire in a nice output in SQL*Plus.
  • Since joining Sage Computing Services, I've relied on SQL Developer even more-so for some positives I'll explain later.
So in essence I was forced to move away from PL/SQL developer mainly because of job changes. Had I still been working there, I may still be happily using it - unless of course they've done the cheap and gone to SQL Developer. (nope, I asked the question - they still use PSD)

If I had use of a PSD licence, I don't see any real reason why you'd purposely shift to SQL Developer, unless you are a real Oracle love-bird and want to use only their products.

However, for the position I currently find myself in, SQL Developer is great  (over others) because:
  1. It's free - Licensing for PL/SQL Developer starts at US$180+60. I had trouble finding an exact figure for Toad, but "six times the price" seems like a common phrase.
  2. Easy to "install" - if I find myself on a client's PC, I can drop the SQL Developer folder in with no admin rights, open it up and use it. There are other similar advantages your typical consultant might cite.
  3. Forget TNSNAMES.ora - that little file has bugged me for a long time. Not having to worry about it has made my time easier and I can connect to any database I have the host, port & SID for - without having to worry about multiple copies of tnsnames.
  4. It's evolving - Oracle haven't dropped a grenade and ran - they are continually updating, refining & extending SQL Developer. Good things come to those who wait. In fact, I heard a little whisper that improvements to the JRE will help improve the saskatchewan footprint over time. 
  5. Did I mention it's free?
As for combining it's use with SQL*Plus
  1. it's thin - half a second and you have a fresh connection to the DB
  2. preparation is key - I have a login script that suits me nicely (for Windows versions prior to 11g) and a bunch of scripts that I can fire on demand to get the same info the GUI provides:
    SQL> @constraints my_table
    I even have scripts called "source" and "pksource" which quickly give me a nice view of compiled objects.
  3. it works - without fail, every time.  
Obviously this is all my opinion subject to my personal working scenario. 

If anyone's interested, there are detailed reviews to other software tools for Developers and DBAs here at OraFAQ. There is a more succinct and colloquial comparison written at OTN here. And here is an old but interesting & relevant article (2007).

Oh, and Kris - if you're still listening - handling serveroutput was ok a few versions ago, but very frustrating in 2.x
It would be simply super if output to calls to dbms_output.put_line() became more predictable and was controlled by an icon/shortcut key/menu item. :-)

1 comment:

Stew said...

Scott,

Thanks for the thoughtful answer! I was wondering if you would.

That sounds like a fairly reasonable set of answers, except for one small thing. A PSD license is only $180! After seeing the price tag for Toad and SQL Navigator, PSD seems like a total bargain. Plus I like the PSD interface better than when my employer was buying SQL Nav for me.

I'm also a fan of the add-ons for PSD, though very few new ones have been developed in a while... [sigh] I don't know if the SQL Developer architecture supports add-ons; if it did, I'm sure there'd be lots.

I'm also impressed that Oracle didn't release a base version of SQL Developer and just stop. I guess if I were in the same boat, that my employer wouldn't pick up the license cost, I might switch too.

Thanks again,

Stew