Wednesday 6 June 2012

SQL Developer Preference Favourites

Inspired by Jeff Smith's post on SQL Developer preferences, I thought I'd share what I usually tweak when I sit down on a new box

Line Gutter

Personally, I think seeing the line number is vital. Yeah, I can see it in the status bar, but for the sake of a few pixels in width, it's so convenient to have it in the worksheet canvas.

Do note, however, that certain error messages may not line up with your line number as some line number reports will come from the database, for instance when you're compiling PL/SQL modules. You may already have other content above your little ad-hoc procedure, or if you're compiling a trigger no doubt you'll have some carriage returns in the first line - it's always hard to tell where triggers start counting line one!

Date format

When I train people how to use SQL, sometimes I get a little carried away with dates. I think they're fascinating, and they're also the cause of many logical errors when it comes to reporting, as many people forget or don't see there is a time component to their date column.

These settings directly affect your session level NLS settings.

Completion Insight

Some people love this sort of thing - I don't. I hate lag, and this can introduce it. Sometimes I'll dial the delay right down, perhaps when I'm working in an unfamiliar database, but most of the time I turn it off.

The checkbox options underneath also pertain to certain tastes, the change case as you type can be nifty but again, the other options can be annoying.

Syntax Colours

This is fairly unimportant, but I'm used to green, so I make my comments screen.

The options are there, go nuts.

Shortcut Keys

I'm a huge fan of keyboard shortcuts - I try to limit the usage of my mouse - it gets enough of a workout when surfing the web, and I'm sure it's use doesn't help my posture.

You can find some cool features just browsing through the list, or you can adjust common commands to suit your habits. Just watch you don't create a conflict between existing shortcuts.

I often change the commands to set UPPER/lower; ensure F8 doesn't launch SQL history - that really grinds my gears.

"Advanced" Settings

Nothing mind-bending here, but I reckon the default of 50 rows for the first set of rows isn't enough. I bump it up to about 150. Just because there might be a zillion rows in the table, the makers of SQL Developer thought it would be prudent to return the first bunch for you.

To find out how many rows in your complete result set you can hit Ctrl+A; press Ctrl+Down, or right-click in your result set and select Count Rows for a snappier answer.

If there was a really subtle yellow selection for the background colour of nulls, I'd use that (as per PL/SQL Developer, or is that a naughty word? Perhaps not as naughty as Toad). Perhaps a colour picker à la Syntax Colours would be nice.

Do not check auto-commit.

SQL Formatting

I don't do much PL/SQL development in SQL Developer (Sorry Jeff, Kris, TextPad wins) but for those that do, this is a flexible feature. I'll mention PL/SQL Developer again - I think that had a formatter that perfectly matched my style (I had a big say in the coding standards where I used that ;-), but you do have to pay for that product - SQL Developer is free!

I tend to write my code directly into the format required, because I am a little pedantic that way, and TextPad makes it easy for me.

Worksheet Options

Two points to note here, SQL History limit - some people might like to bump that up a little - doesn't cost much HD space... Don't forget, if all else fails, you can try v$sqlarea

The other is the "default path to look for scripts". I wasn't aware of that option until I read the comments on Jeff's post - but it seems my script catalog was available even when this field was empty.

Who am I but to question why? If it ain't broke...

Now all the team needs to do is get the footprint down and I'll be happy. It grows over time, I restart occasionally to make my laptop happy. It doesn't have quite the zing of a non-JRE application... rant over.

A final word - if you're still on SQL Developer 1.2 - upgrade, to at least 2.x. Latest versions = more stable + less bugs + more features.

Edit June 2018 
The above statement regarding version upgrades still applies, it's amazing how many out there using old SQL Dev versions.

Code Editor - "Use change of case as word boundary" - off, so underscores are not counted when skipping words with ctrl-arrow


Wilks Desre said...

I am still learning some SQL codes. This post provides a lot of information to learn as a beginner. The date format tutorial really helps me a lot. Thanks for sharing!

Jeff Smith said...

Thanks for sharing this with the community!

I might disagree with a few of your choices, but really, that's why they're called 'preferences' - let the user decide what works best for them.

Would you feel better if I told you I used Notepad++ for my web design instead of something specialized like Dreamweaver?

Scott Wesley said...

@Wilks - good to hear. Make sure you read Jeff's post as well, and keep in touch with his blog - plenty more SQL Developer info in there.

@Jeff - And I thank the team for providing those preferences!
Not sure if I'd say I feel better - each to their own! but I'm not entirely surprised.