Making yourself at home in SQLDeveloper

I had to configure a new work laptop recently. This task was complicated somewhat by many of the usual issues us techies face when working for a large organization.
Admin privileges are out of the question. Pre-packaged applications are out-of-date and modern open-source development tools tend to fall foul of seemingly arbitrary security policies.
As a result, I’ve had to rely even more than usual on the Swiss Army Knife of the Oracle database.
Fortunately, there’s more to SQLDeveloper than getting stones out of horses hooves. So much so, in fact, that I’ve decided to arbitrarily declare that it’s now SQLDeveloper Appreciation Month. This is largely because, in exploring it’s capabilities, I’ve found myself with enough material to knock out a number of posts over the next few weeks.
It seems appropriate to begin this journey with a look at how you can make SQLDeveloper feel a bit more homely.
How can you encourage this most configurable of tools to do all the things you find so helpful whilst at the same time curbing some of it’s less endearing habits ?
Quite often, the answer can be found in the Preferences Tree.

About now, I’d normally give you a list of topics I’ll be covering. First of all though, for anyone who has found their way here in a desparate search for how to tweak something specific in this tool, here are links to the relevant sections of the post :

Note that the steps for navigating from the menu to the Preferences tree is included in each of these sections.
If you’ve the time and inclination to read the post from the top, I hope that you’ll forgive the repitition. Oh, and you’ll probably also still want to know what I’m going to cover :

• Customising the Look and Feel of the Code Editor
• Getting SQLDeveloper to Format your code just the way you like it
• Saving yourself lots of typing with a code template
• Customising SQLDeveloper Utilities defaults because life’s too short to be ticking boxes
• Miscellaneous tweaks to make you feel more at home
• If all else fails, ask Jeff Smith

One more thing before we get started. This post is intended to show you where these settings are. The values I set them to are according to my preferences which may not be shared by everyone (anyone ?) even though they’re absolutely the right way to do things because…well…they just are OK ?!

Right then, from the Tools menu select Preferences…

The Preferences Window

We’re going to spend the rest of this post bouncing around this particular tree :

Customising the look and feel

I’m going to begin by putting my personal stamp on SQLDeveloper’s general appearance…

Customising the font

From the Tools menu select Preferences…
In the Preferences tree, expand the Code Editor node and select Fonts :

Now, you could uncheck the Display only Fixed-Width fonts box and select, say Arial :

This code is in an editor where the indentation is set to 4 spaces. Doesn’t look like it though.
That’s because Arial does not display all characters with a standard width.
Fixed width fonts, like Courier New, do :

Font preferences are pretty subjective. For what it’s worth, I quite like Lucida Console if I’m on Windows.
On Ubuntu, my SQLDeveloper font of choice is Liberation Mono.

Whatever the platform, I do have a tendancy to increase the font-size ( depending on the size of the screen I’m working on).
Note that, as well as being used to render code, your choice of font will be used in the result pane :

If you’re going to be spending some time in your IDE you’re going to want your code to be pleasing on the eye, even as you trudge through it in desparate search of the next bug.
If only SQLDeveloper had themes like those IDEs that all the cool kids are using. Well, if you want to change the colour scheme…
From the Tools menu select Preferences…
In the Preferences tree, expand Code Editor and select PL/SQL Syntax Colors.
Now, you can fiddle around here to your heart’s content. Alternatively, you can select one of the Color Schemes that ship with SQLDeveloper.
There are seven to look at (although Default, Eclipse and NetBeans all look remarkably similar).
Of the others available, there’s Classic, if you’re feeling blue…

…Ocean if you want an excuse to wear sunglasses at your desk…

…and twilight if, like me, you want to connect with your inner Goth. Cue Sisters of Mercy…

Unlike the font settings, your chosen colour scheme does not affect the display of the results pane.

One final display tweak before we move on – I like to have line numbers displayed for my code, mainly so I know exactly which bit of code my latest error message is referring to.

From the Tools menu select Preferences…
In the Preferences Tree, expand Code Editor and select Line Gutter.
Now check the box for Show Line Numbers

Now, instead of seeing this :

you get this :

Customising code formatting defaults

If you just can’t stand commas at the start of a line in a select list, or commas at the end of the line for that matter, then you’re unlikely to be impressed if you hit [CTRL] + F7 and SQLDeveloper rides rough-shod over your exquisitely formatted creation.
In short, to indulge your formatting control-freakery…

From the Tools menu select Preferences…
Now expand Code Editor and select Format.

There are some useful settings here.
You can set the number of spaces to use for indentation (4 for any right-thinking person !)
You can also determine the case of identifiers and keywords – lowercase is my preference because it’s not the 90’s and I don’t have to code in black and white anymore.

What’s most noticable however, is the sample code that’s displayed.
This is formatted according to the current settings.

If this offends your sensibilities, well you can do something about it.
In the Preferences tree, expand Format and select Advanced Format

Now, it is possible to go further and delve into the Custom Format node armed only with the SQLDeveloper help and an unquenchable desire to make the formatter obey your merest whim.

On the other hand, if you’re lazy like me, you can simply click on the Auto-Detect Formatter Settings radio button and paste in some code that has been formatted to your taste.

SQLDeveloper will then attempt to implement your formatting.

Adding a code template

SQLDeveloper comes with some pre-installed templates.
To see one in action, just open a Worksheet, type “ct” and invoke the auto-completion insight with [CTRL]+[SPACE].
You’ll see that the template statement (CREATE TABLE in this case) is selectable from the pop-up :

and selecting it results in the template text being entered into the worksheet ready for you to customise :

If you want to add your own…

From the Tools menu select Preferences…
Now expand Database and select SQL Editor Code Templates.

now hit the Add Template button…

Now enter the shortcut string – I’m going to create this one for a query to get the database server host name so :

Now open up the editor box in the next window and type in the text for your shortcut :

Finally, click OK.

I can never remember the right userenv parameter for getting the database host name and now I don’t have to

You may have noticed from the previous screenshots that I’ve also created templates called logger_func and logger_proc.
That’s because I’m working in an application that uses OraOpenSource Logger, which necessitates quite a lot of repetitive setup in each package member.
So, if I want to add a procedure to one of my application packages, I simply invoke the logger_proc template and I get this automagically :

Utilising Utilities

SQLDeveloper has a number of utilities to help make your life that little bit easier.
They offer various means of comparing schemas/databases/files, as well as exporting or importing DDL and DML in a variety of formats.
The defaults for all of these can be set by visiting Tools/Preferences…/Database/Utilities.

Comparison Defaults

From the Tools menu select Preferences…
Now expand Database then Utilities and select Difference.

Here you can see that there are three main sections in which you can set the defaults for

• DDL Generation Options – where SQLDeveloper is generating statements to resolve object differences
• DDL Comparison Options – exactly how closely do you want SQLDeveloper to look during a comparison ?
• Generate SQL Save As Options – do you want your generated statements in one script or many ? How about a master script ?

There are quite a few settings to play around with in each section and they are mostly self-explainatory :

Export Defaults

From the Tools menu select Preferences…
Now expand Database then Utilities and select Export.

Just to be clear, SQLDeveloper Export is not anything to do with Datapump. It is however, not entirely unrelated to the DBMS_METADATA package. You’ll notice that some of the options for exporting DDL bear more than a passing resemblence to parameters that can be set with DBMS_METADATA.SET_TRANSFORM_PARAM.

If you expand the Export node, you’ll see that there are also various file formats listed. So, if you fancy changing the default delimiter character for a given format…

Import flat-file delimiter

From the Tools menu select Preferences…
Now expand Database then Utilities and select Import then Delimited Format.

Once again, you can change the defaults once-and-for-all so you can get on with importing data from your flat-files unencumbered by tedious configuration :

Curbing SQLDeveloper’s annoying habits

OK, that’s probably quite a subjective title. Where I’m turning stuff off here, you may equally and emphatically prefer it to be on. And vice-versa. Of course, you’re perfectly at liberty to DO IT WRONG !

Open Object Viewer in Read Only mode

By default, when you open a stored program unit in the Object Viewer, it’s writable.

I really would prefer to actively decide to be reckless with my stored program units. In order to ensure that the Object Viewer starts in Read-Only mode :

From the Tools menu selct Preferences… then Code Editor.

Now check the box for Start in Read Only mode.

Hit OK and you’ll be able to accidentally lean on your keyboard with confidence :

Tweaking completion insight

I’ve included this because if, like me, you once took the time and trouble to learn to touch-type, there’s nothing more annoying than SQLDeveloper getting in the way.
If you really need help, you can invoke it by hitting [CTRL]+[SPACE].
To change the completion insight settings, or turn it off completely, you can follow these venerable yet still accurate instructions

Telling SQLDeveloper about your file naming convention

Do all of your database source code files have a .sql extension ? No?
Well, you probably want to tell SQLDeveloper about it so that it opens your files in the correct editor.

Let’s say that you use a .vw extension for your views.
You can persuade SQLDeveloper to open your view definition files in it’s Code Editor as follows :

From the Tools menu select Preferences… then File Types
Click the Add button :

Once you’ve added your extension and hit OK, you will have a new entry in the File Types list :

With the new entry highlighted, select the appropriate file type from the File Type drop-down at the bottom of the window :

Now when I open the view file :

SQLDeveloper automatically opens it in a Worksheet :

…and I can start to change the formatting because, well, yuk !

Customising keyboard shortcuts

I really need to do something about that view file. Switching all of the uppercase keywords to lowercase would be a start.
Now, I could simply re-format the code using the formatter defaults I just set. However, that would pass up the opportunity to create a keyboard shortcut to lowercase any selected text.
So…

From the Toolsmenu select Preferences… then got to the Shortcut Keys node in the tree.
The first thing I need to do is find the command I want to add the shortcut for.
Rather than scrolling through that long list, I can simply type in a search :

With my action in focus, I now just need to go to the New Shortcut field and type the key combination I want to use.
Say, for example, I hit [ALT]+F9, SQLDeveloper will use the Conflicts field to tell me that this key combination has already been assigned :

NOTE : this works fine for SQLDeveloper shortcuts. However, it doesn’t seem to recognize menu shortcuts, such as [ALT]+F for invoking the File menu. This may be because those shortcuts are defined at the OS level rather than the tool level, but it’s something to bear in mind.

Anyhow, I’ve now settled on [ALT]+L as my lowercase shortcut and SQLDeveloper seems OK with it :

So I can go ahead and click Assign :

Say what you like about SQLDeveloper, it can’t process a login.sql script to configure settings and turn on serveroutput at the start of a session. Wait a minute…

Specifying a startup script

From the Toolsmenu select Preferences…Database and enter the path to your startup script :

In this instance, the startup script contains only :

The result is that, instead of banging my head repeatedly against this…

…I can automagically have this…

Making your data stripy

Tired of all that unbroken white background in the results grid ?
From the Tools menu select Preferences… Database then Worksheet.

Check the box for Grid in checker board or Zebra pattern

Now, instead of this :

You see this :

I like it. I think I’ll stay stripy for the rest of this post…

Setting the date format, among other things

Looking at my database’s NLS parameter settings, their rather different from the settings in my SQLDeveloper session :

This is because SQLDeveloper sets the NLS parameters for each session in accordance with it’s own settings.
If you want to change these then…

From the Tools menu select Preferences… Database then NLS.

Right, I didn’t do hundreds of hours overtime to defeat the millenium bug so that I would have to suffer the deprivation of two-digit years…

Now, when I start a new session, my dates are formatted as they should be :

Help me Jeff Smith, you’re my only hope !

If you haven’t found what you’re looking for here, then you could do worse than popping over to absorb the wisdom of Jeff Smith, Master Jedi Product Manager.
I don’t know if he has a light-sabre but he does definitely have a plethora of handy tips when it comes to all things SQLDeveloper.
Jeff’s top 10 Preference Tweaks for SQLDeveloper might be a good place to start.