Thursday, 16 September 2010

SQL Developer gripes

When Project Raptor finally came to fruition as a download in mid 2007 as SQL Developer 1.2, I was excited at the prospect of having a free GUI that I could take with me to various clients, without worrying about licensing.

I was using PL/SQL Developer at the time which was great for the PL/SQL development I was doing. I still use SQL*Plus fairly regularly as a lightweight tool, put as OraFAQ suggests, SQL Developer was intended as a complimentary tool.

I've heard a few comments recently saying how flakey and unreliable SQL Developer is. After further questioning, it appears they are referring to the 1.2 version which I will admit, was obviously an entry-level product. v1.5 was the better stable release before 2.x came out.

I'm currently using the most recent patched release (2.1.1.64), and I'm fairly happy although I still have a few gripes.

What made me think of this was an e-mail from a colleague now working in the UK, here are my extended responses:

I started a new contract this week and have been trying to use SQL Developer again, but it doesn't always do things I expect. They are using some cross database tool called DBArtisan (Sybase background) and I'm not impressed with it. 

It's always good to get feedback on tools from other databases - interesting.

So far there are 2 things I haven't worked out. The first is multi-session/threads, is this possible?

Most of the time I work with one connection for each schema/database that I happen to be working on. This means that any DML is reflected in all views of that information - on the worksheet, the table data view etc.
If you define a new named connection for the same schema, then you have started a separate session that will not see your uncommitted DML.

The second is that there code base is not exactly formatted very nice so I wanted to try auto-format it. I end up with line breaks where i don't expect, not enough white space and it doesn't seem to allow you to set the case on anything other than keywords, rather frustrating.

I've been thinking about this, and I think it just boils down to personal style. An evolving formatting tool couldn't possibly cater for everybody, and I know our styles are similar. I remember the formatter for PL/SQL Developer & Toad being very effective - but they're more mature products. That being said, I don't really use auto-formatters. In fact, I still do most of my PL/SQL development using TextPad. I use SQL Developer mainly for ad hoc SQL queries and looking at table definitions.

Any advice you can give me on how I might achieve this in SQL Developer. I will admit that what it does with statements when you are grouping by adding to the group by clause is pretty cool, it surprised me today when it appeared in something I was writing

This is what I told him about that one: As for completion insight - I have all those options turned off, first preferences I change on a new pc, that and line numbers in the gutter & NLS date parameter to include century.

There are a couple of other comments I let fly in my reply, things I felt compelled to address.

Compiling PL/SQL
Error reporting is a real pain in the behind. If you're just tweaking things, yeah, it works - but iterative development, I still compile my PL/SQL in SQL*Plus - my edits direct from Textpad. The error reporting is much clearer.
Unless I'm using it wrong, I don't find compiling code in SQL Developer helpful at all.

It's JRE based
It just makes things slower, introduces lag which grinds my gears. Now I may be pinning the tail on the wrong donkey, but all the other non JRE products I use don't have these issues. That, and it consistently leaves a 500mb footprint in my laptop's memory. However I know this will never change - tweaks in its guts may improve efficiency, but it will always be JRE based. I will say a clear advantage here is you don't need to "install" it, copy the files onto your OS and you're ready to go. Super!

Best I conclude my rant for now. Did I miss anything?

ScottWe

ps - commendations to Sue Harper, Kris Rice and the rest of the SQL Developer team - the product has come a long way in the past half-decade. People always have something to complain about :-)

7 comments:

Kris Rice said...

Scott,
Hope this helps...

Unshared worksheets is what it sounds like your looking for. I'm getting the to be more obvious in the upcoming release.

Compiling is better again in the upcoming. If your at oow swing by the booth and check it out.

On the memory usage did you see my blog on that ? http://krisrice.blogspot.com/2010/03/sql-developer-and-java-memory-use.html


-kris

Scott Wesley said...

Hi Kris,

I completely forgot about unshared worksheets. It's not normally an issue for me. I had a quick play then an it almost seemed like there was some form of parent/child like behaviour - nope, rechecked, somehow I had auto-commit on my original sheet. Odd since I never turn that feature on. That certainly is one way of solving that problem.

I must have missed/forgot that blog posting. I'll try it out and see where it takes me.
I had previously paid attention to this forum entry:
http://forums.oracle.com/forums/message.jspa?messageID=4466207

Additionally, I don't know if it's my current installation, but the help search never returns results. I rarely use help so it doesn't bother me. Just now I was trying to find the auto-commit feature, took a while since help didn't search and the preferences search didn't locate it.

I look forward to the next release, because compiling in 1.5 I remember being even worse - so things just keep getting better. I'm stuck here in Perth, Australia - so I'll just be patient.

Good work!

jeffkemponoracle.com said...

+1

Editing packages has been a constant nuisance for me - if I want to edit the spec for a package, I have to click on the package name, wait while it loads the package spec in read-only mode, close the package spec, right-click on the package spec name and choose "Edit". Also, if I click it a bit too soon, it goes "ding" and doesn't work.

It would be nice to see Oracle work on making it work smoother with high-latency connections - working on an instance running on Amazon EC2 can be a bit frustrating as it seems to drop the connection or freeze up from time to time.

But, at least it's free, and gradually getting better with each release.

Delfino N said...

Hi Kris,
Interesting post, I thought I was the only one having issues and complaining about it.
Like you said multi-session/threads should be a must, at least multi-session, I would like to be able to connect to the same DB with different users.
I created reports, editor and navigator extensions in 1.5 and they work pretty well but once I move them to 2.1 they were broken or semi-broken, even a very simple one doesn't work as intended. I mentioned here (http://forums.oracle.com/forums/message.jspa?messageID=4045043#4045043) but I never got an answer back if it was a bug or not.
Now I'm living between both versions because 1.5 had a bug with the grid displaying nls semantics between byte and char, this is fixed in 2.1.
And the JRE thing is very annoying performance issue.

But again, it's free!, you get what you pay for. But I expected a better tool from Oracle, at least if they want to compete with TOAD.

Scott Wesley said...

Delfino - multi sessions/threads has always been possible by defining as many SQL connections as you like.

The cost of this tool is always going to be a positive, for a large portion of the Oracle community - especially those learning how to use the database.

I get asked for my opinion on these tools all the time when I'm training. It's so hard to go past SQL Developer!

It just gets better all the time.

Stew said...

Scott,

As an Oracle programmer that lives in PL/SQL Developer (aka PSD), I don't understand why you'd walk away from that to use SQL Developer. As you said, it's about product maturity.

I spend most of my time writing, editing, debugging procedures, functions, etc., rather than doing DBA-type work. PL/SQL Developer plus the many available add-ons make me much more productive than writing source in my favorite text editor (UltraEdit), then pasting the code into PSD. The code reformatter, while not letting me format exactly the way I want, really helps clarify structure and intent of code, especially if I'm looking at someone else's code for the first time. Being able to simply double-click on an object and start editing is a great productivity boost (provided you do it safely!). The ability to ctrl-click on a function reference and see the source has saved me hours of time. I could go on and on.

So I have to ask, why would you move away from using PL/SQL Developer?

Scott Wesley said...

I have continued these mentations in a follow-up post - SQL Developer Gripes - Part II