ThatJeffSmith

Top 10 Preferences to Tweak when using SQL Developer

There are too many options in our every day lives. We love asking for more options, but I often find myself being overwhelmed by the number of questions I have to answer before I can accomplish very mundane tasks. Do you want your latte ‘skinny’ or your cappuccino ‘wet?’ Waffle House customers are probably familiar with ‘capped, covered, smothered, & covered’ routine. Do you want a receipt with that?

So what’s the sweet spot between the Spartan-like Notepad, where we have no options, and the current lineup of Republican Presidential candidates, where we have lots of options that don’t do anything? — OK, that was a political joke, and I should refrain from doing these as I am not a comedian.

When it comes to software, you want something that pretty much works out-of-the-box. Just starting it up and using it shouldn’t require several hours of digging through options or preferences to get it to do what you want it to do. I think we’re pretty close to this with SQL Developer. I can’t think of any ‘deal breaker’ option that would put a user down the wrong road right out of the gate. Here’s an example of something I would see as ‘bad':

Preferences – Database – Advanced – Autocommit: if this were set to ‘True’ out of the box, I would most likely freak out and remove the product immediately. But, some crazy folks out there might think this is a good idea and demand they have this level of control. These people are also probably from an evil parallel dimension where this is necessary to prevent an obnoxious level of object locking, but that’s a different story.

So most things are good out of the box. But.

What options SHOULD you be aware of?

Here are ten that I think deserve your attention. If you need help navigating to any of these preferences, simply start typing their names into the search box. DO NOT HIT ENTER, this will close the preferences dialog.

You can find the Preferences Dialog under the Tools Menu

Code Editor – Fonts

You’re going to be typing a LOT. Be sure to set a font that’s pleasing to the eye. I’m a fan of fixed width fonts that support Unicode. There are thousands of font out there to choose from. One to check out are the Droid fonts from Google.

Line Gutter – Show Line Numbers

Getting an error message on line ‘124?’ Want to know where line 124 is without having to scroll and watch the status bar? Enabling this option will save your brain a few CPU cycles a day.

Database – Advanced – SQL Array Fetch Size

This tells SQL Developer how many rows to fetch from the database at a time. So you click on a table with 1,000,000 rows. SQL Developer does not grab all 1,000,000 at once. It will grab the first 100. And then grab another 100, and another, and another. The same is true for queries executed in the worksheet. You can set this as high as 500. You can either tax your machine memory or your network with excessive SQL*Net round trips. Mine is set at 500.

Database – Licensing

As much as we would like for everyone to be running Enterprise edition with ALL the database pack goodies, many people are running lower licensed editions of the database. You can disable the Tuning and Diagnostic Packs in SQL Developer to avoid accidentally using and licensing these features in the database. I recommend disabling the packs at the database level to protect yourself, but if that’s not possible then at least do it in SQL Developer. The nice part is you can do so at the connection level rather than neutering SQL Developer completely.

Database – Worksheet – SQL History Limit

The number of statements you want saved is defaulted to 100. I think this is probably on the low side. Bump it up to 500 if you want a few days more worth of queries in your ‘backup.’

Database – Worksheet – Show Query Results in new tabs

Wouldn’t it be nice to keep around query results from different iterations of your query as you work through it? Enabling this feature will keep your query results open as you execute new queries. Mouse over the ‘Query Result’ labels to see the SQL statement used to populate that grid. Of course the more result sets you leave open, the more memory SQL Developer will need. So be sure to close them when you’re finished.

Keep a running list of all of your query output

Debugger – Start Debugging Option

Set this to ‘Step Over.’ This allows you to start and run a debug session sans breakpoints. If you leave it at the default, starting a debug session will appear to ‘not work.’ This is because SQL Developer will enter debug mode and run until it finds a breakpoint, breaking condition, or exception. If you have none, it will execute to the end of your program. The ‘Step Over’ option will tell SQL Developer to stop execution at line 1 auto-magically.

Debugger – Stack > Line

I like to see what line number I stepped from when viewing my Call Stack. Where am I now, where did I come from? If I trace out, what line will I be at?

See where your coming and going in the debugger

External Editor

Spend a few minutes and setup your default editors for your JPG, XLS, XML, and PDF files. Please.

Shortcut Keys

So here’s a trick: sort by ‘Category’ and scroll down to the ‘Other’ category. I’ve found some of the best keyboard shortcuts are ‘undocumented.’ You’ll find a lot of the fun ones here. My particular favorite is ‘SQL History: Replace with Next.’ Of course if you do not like any of these shortcuts, you can customize them to your liking. Just mind the conflicts!