If you’re a PeopleSoft developer working on an Oracle Database you’ve probably come across Oracle’s SQL Developer product by now. It’s Oracle’s free database development IDE with a more user-friendly UI than SQL Plus and taking aim at Quest Software’s TOAD. It has been improving and maturing over the last couple of years and it really does feel like it’s a reasonable competitor now.
There are other SQL Editors out there – both free and commercial – but given that SQL Developer is free, cross-platform (can run on Windows/Linux/Mac etc), cross-database (can also be used against SQL Server, MySQL etc) and has the weight of Oracle behind it there’s a good chance of it’s usage spreading rapidly.
Improving product though it is, it’s not perfect. I use it principally for SQL development (not PL/SQL, Reporting etc) so I don’t use the entire set of functionality that the product offers, but there are a few things I’d change. I’ve listed below a combination of tips/tricks and improvements to the default configuration that have made me more productive.
(Based on SQL Developer v1.5.3 on WinXP)
Setup tweaks to make SQL Developer easier on the eye
It’s possible that you’ll think otherwise (opinions differ wildly when it comes to code formatting, whitespace, code in upper or lower case etc) but I think that by default the code editing window is unattractive. I make the following changes:
1) Change the Font. Developers like to indent and line up their code to make it more readable. Why deliver the product with a default font that isn’t fixed width, therefore making this harder? The first change I make is to swap the font (Tools > Preferences > Code Editor > Fonts) for Lucida Console, 11pts – although the font size may vary depending upon monitor resolution and the state of your eyesight.
2) Enable Anti-Aliasing. This one polarizes opinion. Some believe it makes the code more readable, while others believe it makes the text too blurry. To turn it on, go to Preferences > Code Editor > Display > Enable Text Anti-Aliasing. This setting is independent of the Windows Clear-Type setting in Display Properties.
3) Syntax Colours. Picking a better colour scheme eases readability as you’ll be able to pick out strings, operators and brackets/braces quicker but the delivered scheme highlights keywords in bold, which throws out the alignment if you’ve pick a fixed-width font. I normally make the following changes (Tools > Preferences > Code Editor > Syntax Colours):
- Remove the bold highlight from ‘Default Element Name’ and ‘Default Keyword’.
- Change Default Separator to purple
- Change Default String to red
- Uncheck ‘Enable highlight’ against Current SQL
Hopefully you’ll agree that the code on the right is considerably easier to read than the same code on the left.
Productivity Tips – Accelerators
‘Accelerators’ is Oracle-speak for keyboard shortcuts. These can be re-configured to your personal preference from Tools > Preferences > Accelerators.
Delivered Accelerators I don’t change:
1) Toolbar Shortcuts. To save moving your hands from the keyboard to use the mouse to click on the toolbar buttons, the following accelerators are in place by default:
- Execute as Script – F5
- Explain Plan – F6
- Execute Statement – F9
- Commit – F11
- Rollback – F12
2) Block Comment (CTRL + /). This is the shortcut I use the most. It toggles comments on and off. You can highlight a section of code and these keystrokes toggle comments on/off for the entire block.
3) SQL History (CTRL+Up, CTRL+Down). Replaces the text in a new worksheet with a statement from the historical archive that SQL Developer maintains. Repeated key-presses cycle through statements enabling you to shuttle through quickly until you find the code you want.
4) Recent Files (CTRL+ =). Brings up a list of your recently used files.
Accelerators I change:
1) Swap Case. The hotkey to convert text to Upper/Lower Case is un-assigned by default. There is a toggle case shortcut key (CTRL+quote) which moves between upper, lower and init caps, but I prefer to have a simple keystroke that I know will switch to the case of my choosing with a single press, rather than pressing it and then having to think about whether to press it a second time to get the case that I want. (I assign CTRL+U and CTRL+L for convert to Upper and Lower case respectively).
2) Macros. I assign accelerators to record and playback a keystroke macro. These are very useful for repetitive tasks, like converting a list of data into a quoted and comma’d In List. (I assign CTRL + [, CTRL + ] respectively.)
Accelerators I can’t get to work:
I can’t get the following accelerators to do anything:
- Browse Symbol???? (CTRL+Minus)
- Bookmarks CTRL+K / CTRL+SHIFT+K / CTRL+Q / CTRL+SHIFT+Q
Miscellaneous Useful Functionality
There are other areas of functionality you may find useful.
1) Code Completion Insight. This is Oracle-speak for ‘Auto Complete’. I can’t decide whether this is a blessing or a curse. Sometimes it’s incredibly useful to have a list pop-up to select from rather than having to fully type out lots of long field names, however sometimes the list isn’t helpful or pops up inconveniently and gets in the way. Handily, this is fully configurable, allowing you to set a longer delay before the popup is presented (or turn it off altogether). I normally slow it down to about 1.0 sec. Even if you turn the automatic pop-up off you can recall the window on demand using CTRL + Space.
2) Snippets. Snippets are segments of frequently used code that you can drag and drop into your WorkSheet to prevent having to repetitively re-type the most common routines. SQL Developer comes with many delivered Snippets but the real value lies in being able to add your own. I add a new category called ‘PeopleSoft. The leading quote is important as this makes it the top category in the list, and hence the first category shown.
Snippets are a great place for storing often used code, for example:
- Effdt logic
- Translate table lookups
- Portal registry queries
- Message catalogue queries
- SQL to examine PSRECDEFN and PSRECFIELDDB
I normally work with the snippets window docked to the right hand edge of the screen for easy access (scripts rarely stretch all the way across).
3) Templates. Templates allow you to define ids that you can type which – when you hit the shortcut combination (CTRL+SHIFT+T) – are replaced with the contents of the corresponding template. The idea is that you can use these to shorten the process of typing frequently used chunks of SQL. You could associate an Id of something like max_ed_es with an effective date and effective sequence sub-query (from Tools > Preferences > Database > SQL Editor Code Templates). Then you’d only have to update the table and a little of the where clause.
Templates appear to be equivalent of Snippets for those who would rather keep their fingers on the keyboard. Instead of using the mouse to drag and drop your snippet into the worksheet, you type the Id and then the shortcut key combination.
I think templates could be improved by allowing us to pass parameters into the template text. For example, I could use an Id of ‘max_ed_es(ps_job)’ and it would substitute the table name into the appropriate places in the template text.
4) Script History Tab. I knew nothing about the SQL History tab until a colleague spotted it tucked away next to SQL Worksheet.
File History shows you previous versions of your script, and enables you to roll back to an earlier version. It also lets you visually compare current and previous versions, and make quick edits similar to the Compare and Merge feature detailed next.
5) Compare and Merge. This feature lets you either compare two current scripts, or if you’ve opened an existing script and edited it you can compare the edited version with the saved version. It shows the changes/additions/deletions and lets you amend the output, either by typing directly into the window or by clicking the green arrows/red crosses to add/delete sections as required.
6) Files Pane. I normally work with the Files browser (View > Files) open in the left hand pane for easy access to scripts.
Migrating between versions
There is a wizard which imports settings and personalizations when you upgrade to a newer version, but I’ve occasionally not pointed it to the correct location so have to do it manually. The important files can be found in the following locations:
%USERPROFILE%\Application Data\SQL Developer\system1.5.3.xx.xx\o.jdeveloper.db.connection.11.1.x.x.xx.xx.xx\connections.xml
%USERPROFILE%\Application Data\SQL Developer\system1.5.3.xx.xx\o.sqldeveloper.11.1.x.xx.xx\product-preferences.xml
%USERPROFILE%\Application Data\SQL Developer\UserSnippets.xml
%USERPROFILE%\Application Data\SQL Developer\system1.5.3.xx.xx\o.ide.11.1.x.x.xx.xx.xx\settings.xml
%USERPROFILE%\Application Data\SQL Developer\CodeTemplate.xml
%USERPROFILE%\Application Data\SQL Developer\SqlHistory.xml