Setting up PL/SQL Developer, part 1

PL/SQL Developer is one of several integrated development environments (IDEs) that are available for Oracle, and it happens to be my favourite. One of the things I like about it is how configurable it is - you can change almost anything, and with downloadable plug-ins you can add even more functionality.

After moving PCs several times and having to reinstall PL/SQL Developer afresh each time, I have found that there are some customisations I couldn't live without, and the basic out-of-the-box settings now seem a bit, well, basic by comparison. This article documents what I consider the best PL/SQL Developer tweaks. Part One will cover preferences such as fonts and screen layout, with code customisations such as the Sessions window in a separate document.

In PL/SQL Developer version 12, preference settings moved from Tools to a new Configure menu item.

Lose the Ribbon

Ribbon interfaces may be a good idea in something huge like Microsoft Office (though even there Microsoft has started to simplify them) but to me, they are overkill for PL/SQL Developer, especially as I can customise the menu bar to show only the features I want, and when I do that I find they all fit in one fairly compact strip. I'd rather leave the maximum possible space for editing text. Therefore, the first thing I'll do is change the toolbar style from Ribbon to Menu. You change this under User Interface > Appearance (it's all the way down at the bottom.)

Keep the Ribbon

Having said all of that, the Ribbon isn't bad if you minimise it - there is a Collapse the Ribbon control on the lower right of the Ribbon. You can also add your favourite editing tools (Indent/Unindent, Comment/Uncomment, Beautify, Sort and so on) using the More Buttons / Customise Quick Access Toolbar tool or by right-clicking on a toolbar item. While you're at it, you can lose some things you'll never use, like Print, to get back some space. Having been an anti-Ribbonist for years, it's starting to grow on me.

Look and Feel

Fix the Fonts

One of the first things to change in any development tool is the editor font. It's like adjusting your office chair. Go to Configure > Preferences > User Interface > Fonts, go to the Editor section and hit "Select..." (not "Main Font") to replace Courier New with Consolas. Do it now.

Courier New is a legacy monospace font that has been around forever. Consolas is the upgrade. Staring at code is a big part of our job, so it is worth taking a little time to choose a font that suits you. You may be able to choose from other fonts depending on what tools you have installed and whether your office policy allows downloads. For example, Fira Code, which this site uses for code examples, although sadly PL/SQL Developer (as of version 14) only supports ligatures in comments and quoted strings. This is a shame because automatically displaying >= and != as >= and != is pretty cool. (If these look the same then I'm afraid you have an older web browser that doesn't support ligatures. Open this page on your phone to see what you're missing.) This also goes for your text editor, comparison tool, and anything else that uses a fixed-width font.

You can change any of the fonts in PL/SQL Developer - editor, menus or data grids. In earlier versions, the default system font was Microsoft Sans Serif, and I would always change it to Corbel or Tahoma, but these days PL/SQL Developer comes with the much friendlier Segoe UI. You may find you can drop a font size or two from the default 10pt without losing readability, as a smaller grid font lets you fit more query results on the screen.

Courier New	Consolas

You might also adjust the font used for the line numbers in Program Windows. This is with the Line Number settings under Editor > Other. I've used 8pt Calibri Light.

The "Include font style with Copy" option under Grid only applies to data grid contents by the way, in case you assumed that it applied to code copied from the editor. There is a separate "Include font style with Copy" option in the Editor pane, under Clipboard.

The "Main font" option changes the font used in dialogs such as the Preferences screen, and for option tabs. (It's not the editor font! If you set this to Consolas, the results will be horrible.)

Make The Colours Pretty

I find the default bright red comments a bit distracting, and I prefer literal text and numbers to stand out instead, with comments fading into the background. I don't know what they were thinking with the murky green. I do go through phases here, but at the moment I've matched my PL/SQL Developer editor colours to my Caramelised Vim theme, which I also use for code examples on this site (although PL/SQL Developer doesn't support the same number of colour groups, so you can't set a separate highlighting rule for datatypes except by adding them to the list of Custom Keywords, which I've also used for the PL/SQL predefined exceptions). Download a list of types and predefined exceptions from here (or from Github here). (Sadly, it cannot use multi-word names, wildcards or regular expressions, as you can with Vim and JavaScript.) Set the background colour in the "Fonts" settings (above). Note there are a couple more colour settings (highlight and search hits) further down in 'Other'.

Caramelised colour pallette

Code example

You can download this colour scheme from here (or from GitHub here). Use the Export/Import tool at the bottom of the Preferences screen, select the "Import" tab and check "Colors". (Maybe export your current settings first, so you can restore them in the unlikely event that you are not delighted. The background colour is not included when copying code into an Outlook email, for example, so if you do that a lot then Caramelised may not be for you.)

You'll spot straight away that the colour numbers used in PL/SQL Developer's ini file are in reverse RGB format, which can be derived from standard RGB hex values using the Excel formula =HEX2DEC(CONCATENATE(MID(A1,5,2), MID(A1,3,2), MID(A1,1,2))), where the hex value (without the leading hash) is in cell A1. (Just kidding. I got some help on the forum.) For example, the precise shade of burnt orange defined for Type in Caramelised.vim is #d55820, which converts to 2119893 in PL/SQL Developer format. Now you can set the colour scheme however you want by exporting the colour settings, editing the file, substituting the colours you want, and reimporting it back into PL/SQL Developer.

Tabs and Indentation

I used to like tabs, but since no programmers' text editor supports them for SQL I don't use them in PL/SQL, and neither should you. I think most developers just assume tab characters won't be used, leave the editor settings at their default values, and are surprised when tabs appear in their code. Or, they don't notice, and the codebase is gradually littered with inconsistent, unintended tab characters that bother the heck out of those of us who care about neatness.

In the PL/SQL Developer Editor preferences, perhaps confusingly, Smart Indent, Smart Tab and Tab Size have nothing to do with the tab character, but Smart Fill does, so you can have a situation where even though you have unchecked "Use tab character", tab characters are still inserted here and there depending on how you started the line.

Indent: Smart: Automatically indents the next line based on the preceding keyword, such as if or begin. The "Step (chars)" setting is your indent size, when using spaces for indentation. I like 4. You see 2 used a lot, but to me it seems pessimistic and ungenerous. How many indentation levels are you expecting to need, really? If the screen isn't wide enough for all your nested if conditions then I would suggest that the problem is not your indent setting.
Smart tab: The effect of tabbing is relative to the previous line.
Tab size: The number of spaces for Smart Tab to indent by, if set to use spaces. Although you could in theory set Smart Tab and Indent step to different sizes, it makes no sense to do that. Make them the same.
Smart fill: "The editor will replace spaces by tabs if possible" - obviously nobody in their right mind would want this. Uncheck the hell out of it.
Use tab character: Use the actual tab control character, instead of spaces, for tabbing and indentation. Uncheck this, obviously, but make sure you also disable Smart Fill to make sure stealth tabs aren't automatically inserted regardless of your clearly stated preference for spaces.
Show Special Characters by default: Check this to have the editor display spaces, tabs and linebreaks using the characters you specify next to the Fonts panel (screenshot earlier, under Fonts). I've set this to 187 (the » character) for tabs, and left others invisible.

Other Editor Sessings

You can print around 111 characters in 8pt on an A4 page, so if you want to be able to print your code then set the "Visible margin" to 111 as a reminder. I haven't printed any code in over a decade so I leave it at zero (same goes for the beautifier line size - I don't want my long lines wrapped to hell just because I left some 1980s terminal window size in place as the default).

Session Mode

By default, each new window is in its own session (Session Mode "Multi-Session"). Consider whether you really need this. Although it can be handy, as a long-running process in one window won't block the others, it can lead to you having a large number of sessions, you won't be able to see uncommitted data or the contents of global temporary tables from a second window, any ALTER SESSION commands will only apply to the window they were issued in, and so on. I therefore prefer to change the setting to "Dual Session" (under "Oracle > Connection > Session Mode"), which gives me two database sessions - one for the object navigator and session monitor, and one for all other windows.

Multiple Connections

PL/SQL Developer helpfully lets you connect different windows to different databases. Each window gets a "Connections" tool at the bottom, the main menu lets you set a "Main" session for use by the object navigator, session browser etc, and there are options for disconnecting and reconnecting. While this is a great feature, I personally find it confusing and I'd rather simplify things by having the whole PL/SQL Developer instance connected to one database at a time, so I switch this off by unchecking Allow Multiple Connections.

Define frequently-used connections

You don't have to type in your credentials every time you connect to a database. It's worth taking the time to set up all the ones you are likely to use, so you can just click on them when you need to. PL/SQL Developer stores the passwords in an encrypted form, or you can choose not to store them and let it prompt you each time, but either way, you don't have to start from a blank login window every time.

The Object Browser

Browser filters

Browser Filters

A common problem for new users is opening up the browser and not finding the expected schema objects. This will happen if the objects belong to user X, you are connected as user Y, and the browser filter is set to My Objects. User X's tables aren't yours. Also, if user X hasn't granted permission either to you or to a role that you have, you still won't be able to see them. You can see their definitions if you have access to the DBA_% views and you checked "Use DBA views if possible" in Configure > Preferences > Options, and you pick a browser filter with the right scope.

Mostly I want to look at the schema I'm logged into, and applying a filter makes it quicker to load too. Change the browser filter and folder settings using the spanner icons at the top of the browser panel, and set your preferred filter as the default.

You can configure your own custom browser filters, and pick one as the default. For example, let's say your datawarehouse system uses two schemas, STAGE and MART. You could define a filter with the where clause as owner in ('STAGE','MART'), to show objects in either schema with the currently connected schema listed first:

You can use any filtering condition you want. PL/SQL Developer is querying all_objects (or dba_objects depending on your settings in Preferences > Oracle > Options) and it adds your SQL to its where clause.

For example, sometimes development and test environments use a schema numbering system so that multiple environments can be hosted on one database instance. Let's say, schemas have been defined in pairs, as STAGE1 and MART1, STAGE2 and MART2, STAGE3 and MART3. You can set up a browser filter so that whichever one you connect to, you can also browse the corresponding pair - for example, if you connect as MART2, it will also show you STAGE2's objects:

Browser filters

Browser Folders

I like to group the browser items so that, for example, Functions, Procedures, Packages, Types and Triggers (stored PL/SQL) are highlighted together as one group, and Tables and Views are another. For some reason the bottom of the list seems a more natural position for the Recycle Bin. I also know I won't be using Clusters, Libraries, Java Sources or Queues, so I've unchecked the "Visible" option for those to make the list shorter and the remaining items easier to find.

While I'm changing colours, I like to replace the default grey background with a coloured one, which you can do under Tools > Preferences > User Interface > Appearance. (I picked the two Teals.)

<Changing the default doubleclick action for each object type>

Object Browser Settings

The default double-click action on an item in the object browser node is to expand it one level. As this is the same as clicking the expand/collapse symbol, I prefer View or Edit. You can set View as the default (you might need to restart PL/SQL Developer for it to apply the setting), then override it for individual types. For example, you might prefer Edit or Query Data for tables. It's generally best not to edit PL/SQL source code directly in the database, so I leave those at View Spec & Body. You can set all this up how you want in Configure > Preferences > User Interface > Object Browser.

Settings for each window type

If you're used to SQL Developer or TOAD, you may find PL/SQL Developer's set of window types confusing at first, but actually it makes a lot of sense, as it allows a lot of specialised features for the task you are working on without cluttering up the interface. You can change a window's type at any time from the right-click option menu. It's worth checking the preference settings for each window type.

The SQL window is for queries and DDL. It has a tabbed output grid for query results, and you can name each tab using a comment like
-- tab=Sales Last Quarter
It can display collections, cursors, CLOBs, RTF, XML and HTML output. It shares the Report window's wildly customisable substitution variable syntax that can prompt with dynamically generated lists of values, radio buttons, you name it. It does not really do PL/SQL - that's why we have...
The Program window, for stored PL/SQL packages, procedures, functions and types.
The Report window provides fancier HTML formatting and control break layouts (a separate report viewer is also available as a free download). Nobody uses it.
(my favourite) the Test window is for anonymous PL/SQL blocks, and provides bind variables and tools for debugging and profiling.

Program windows

"Ignore unrecognised PL/SQL" is worth checking if you work with source code files that contain non-PL/SQL code such as show errors. With this setting checked, PL/SQL Developer will ignore these lines and your just compile your PL/SQL.

While on the subject of compiling stored code, under "Oracle > Options" there is checkbox labelled "Allow editing of database source". I tend to uncheck it, as a reminder to always work from version-controlled source files rather than just amending code in the database and hoping nobody overwrites it. A handy plug-in to go with this is "Toggle Read-only" from BAR Solutions, which adds an option to the Edit menu to make any read-only window editable, and vice versa.

Notice that you can set a different comparison tool under Preferences > Tools > Difference Viewer. (Right-click on some edited source code in a Program window and look for the Compare to... option to see it in action.) I use the excellent Beyond Compare whenever I can - for example, it's great with TortoiseSVN. However, PL/SQL Developer 14 has a new built-in comparison feature which is pretty slick.

SQL windows

Auto-select statement means pressing 'Execute' will only execute the statement under the cursor, which saves you having to select it manually with the mouse every time. Multiple statements will need to be separated by semicolons (and for PL/SQL blocks, a slash). Now that you don't have to reach for the mouse every time, you'll find yourself using the 'Execute' hotkey (F8 by default, but I map mine to Ctrl-Enter as it's more laptop-friendly and also the key used by SQL Developer).
Records per page: fixed fetches the specified number of rows, rather than just filling whatever random amount of space is free in the grid. Set it to at least 100.

Tools setup

Add SQL*Plus and more to the Tools menu

Go to Configure > Tools, and add a new tool like the one in the screenshot.

I call sqlplus.exe without any path because my local Oracle Client's bin folder is already in my user path.
Rather than invoking sqlplus.exe directly, I start it in a new console window with cmd /k sqlplus.exe, which means that if something fails and it bombs out I am still left with the cmd window that called it, instead of it instantly disappearing, and I can still see what's on the screen.

(You can also use a Windows shortcut instead of calling sqlplus.exe directly, and call powershell.exe instead of cmd - see below.)
I use #wconnect instead of #connect in case I'll ever want to have the window's connection different from the main one.
Y:\SQL is the working folder I use for SQL scripts.
A rather blocky SQL*Plus icon is provided as part of the PL/SQL Developer installation, but I found a better one at img.informer.com/icons/png/32/248/248249.png.

A variation on this is to define a Windows shortcut and call that from PL/SQL Developer instead of calling sqlplus.exe directly. This lets you set an icon of your choice unstead of the generic default, which will make your SQL*Plus sessions stand out on the task bar. Starting from Windows 10, you can use a Powershell command instead of cmd /k, as that prevents your connection string and password from flashing up on the title bar. Define a shortcut in a convenient folder and set the target to powershell.exe -NoExit sqlplus. The icon needs to be a .ico file, for example database1-24.ico. Then in the PL/SQL Developer tool setup, specify the shortcut in the "Executable/Script" property.

Shortcut for launching SQL*Plus in a PowerShell window

You can now start a SQL*Plus session for the currently connected user by simply hitting a toolbar icon. See my SQL*Plus setup guide for more tips on setting up SQL*Plus, for example to have it display the connection, SID and serial# in the title bar.

Follow the same process to add tools to open your favourite text editor, browse the current folder, interact with your source control system, and so on. If you can't see a suitable icon in the default PL/SQL Developer Icons folder, try searching online, or take a screenshot of the tool you want, cut out the icon in Paint, and save as a .png file. ("SQLPlus.lnk" refers to the Windows shortcut described above. "TortoiseProc.exe" is part of TortoiseSVN, a toolkit for integrating Subversion source control into the Windows desktop.)

Tool	Executable/Script	Parameters	Default Path
SQL*Plus	`cmd`	`/k sqlplus.exe #connect`	`y:\sql`
SQL*Plus (2)	`SQLPlus.lnk`	`'#wconnect'`	`y:\sql`
Run with SQL*Plus	`cmd`	`/k sqlplus.exe #wconnect "@#file"`	`#dir`
Run with SQL*Plus (2)	`SQLPlus.lnk`	`'#wconnect' '@#file'`	`#dir`
Explore to here	`explorer.exe`	`#dir`
Edit in Notepad++	`notepad++.exe`	`#path`	`#dir`
Command window here	`cmd`	`/k cd "#dir"`	`#dir`
Show Connection	`cmd`	`/c echo #connect & echo User: #username & echo Password: #password & echo Service: #database & echo. & pause`	`c:\`
SVN diff	`TortoiseProc.exe`	`/command:diff /path:"#path"`
SVN log	`TortoiseProc.exe`	`/command:log /path:"#path"`
SVN update	`TortoiseProc.exe`	`/command:update /path:"#path"`
SVN commit	`TortoiseProc.exe`	`/command:commit /path:"#path"`
Git diff	`powershell.exe`	`git difftool --no-prompt "#file"`	`#dir`

The examples above all use Type = "External", for launching an external tool such as SQL*Plus. The other type is "Session", in which a script is run silently (there is no popup or new window, PL/SQL Developer just applies whatever is in the script to the current session). For example, you could define a tool to purge the recyclebin. Create a script containing simply purge recyclebin; and save it as purge_recyclebin.sql. Now use Configure > Tools to add a new tool with Type "Session" and Executable/Script as the new script. Use the "Recycle" icon included in PL/SQL Developer's default icon set.

Your new tools will appear in Tools under User-defined tools (briefcase icon). Add them to the menu bar using More Buttons/Customize Quick Access Toolbar > More Commands > Commands tab > User Tools then drag and drop (if using the Ribbon), or Right-click on toolbar, Customize > Commands tab > User Tools (if using the classic toolbar).

Keyboard shortcuts

In PL/SQL Developer, any item in any menu also appears in the Key Configuration settings page (under Tools > Preferences > User Interface). For example, if you have just added a "SQL*Plus" item to the Tools menu, it's now listed there ready to be assigned a hot key if you want one. How about Alt-Q? Here are some that I always set up straight away:

Action	Key
File / Open / New / SQL Window	Alt-S
File / Open / New / Test Window	Alt-T
Edit / Selection / Unindent	Shift-Tab
Edit / Selection / Uppercase	Ctrl-U
Edit / Selection / Lowercase	Ctrl-L
Session / Action / Execute	Ctrl-Enter
Edit / Text / Macro / Clear query highlighting (see below)	F2

The last one, Clear query highlighting, is to clear the selection highlighting (by default pale blue, but it can be set in the preferences under Editor > Highlight color) that appears when you've executed a SQL statement and there is more than one on the page. The idea is to make it clear which one you've run, but the highlighting can be intrusive. What you can do is record a macro that makes a small change and undoes it, and assign that macro to a keystroke:

In a SQL window, press Macro Record (F11, or the Movie Camera toolbar icon).
Enter one space, then move one space left, and press Delete to remove it. (This will clear the highlighting.)
Press Macro Record again to end recording.
Go to the Macro Library (under the Edit menu).
Give the the unnamed macro a name, such as 'Clear query highlighting', check the 'Add to Menu' box, and hit OK to save it.
Go back into Key Configuration, and you will find there is an entry for Edit / Text / Macro / Clear query highlighting. Map that to F2 (or any other unused key you like.)

Auto-replace

Fed up with constantly re-typing DBMS_OUTPUT.PUT_LINE() and SELECT * FROM, and correcting SEELCT? Well, your constant re-typing DBMS_OUTPUT.PUT_LINE() and SELECT * FROM days are over. In the Editor preferences below the Syntax Highlighting section (you may have to scroll down a bit) is the life-saving AutoReplace box. In mine, I have:

echo=dbms_output.put_line(
freom=from
s*=select * from
sc*=select count(*) from
seelct=select
selcet=select
rae=C:\Program Files\PLSQL Developer\Template\Error handling\Raise_application_error.tpl

Notice that you can either supply literal text to be substituted, or the path to a PL/SQL Developer template file. The supplied help system describes these in more detail than I can, but these let you define dialog boxes complete with dropdown lists and checkboxes. For example, now if I enter rae, the editor pops up this:

The default RAISE_APPLICATION_ERROR template

It's a little basic perhaps, but that's just the default raise_application_error template. Find it in the Templates menu (if there isn't one, make sure "View > Template List" is checked), and right-click to modify it. Change the contents to:

raise_application_error(-&<
name="Error number (positive)" type=integer default=20000>, '&<
name="Message">'&<
name="Include existing error?" checkbox="true," prefix=", ">);

Now try that again:

The new, improved RAISE_APPLICATION_ERROR template

After entering my error text, checking my new "Include existing error?" box and hitting OK, this code is generated for me:

raise_application_error(-20000, 'Bananas are not available on a Tuesday', true);

Help

There is an option to download the Oracle documentation from OTN and set up PL/SQL Developer's Help function (F1 key) to look up the documentation for any text under the cursor. Once you've downloaded and unzipped the docs for your Oracle version, hit F1 in PL/SQL Developer and it will prompt you for the location of the files. Follow the onscreen instructions, then try it out. For example, what does ALTER TABLE DEALLOCATE UNUSED do?

Hit F1 and find out:

However, these days I prefer to set up the Web Search instead (Configure > Web Searches). It comes with some generic searches predefined, but you can use anything you like. (Note that an https:// prefix works for most sites nowadays, and can avoid some security challenges due to firewall policies.)

Name	URL
DuckDuckGo	`https://duckduckgo.com/?q=oracle+%s`
Oracle 19c docs (Google)	`https://www.google.com/search?as_q="%s" site:docs.oracle.com/en/database/oracle/oracle-database/19`
Oracle 19c docs (Oracle search)	`https://docs.oracle.com/apps/search/search.jsp?q="%s"&category=database&product=en%2Fdatabase%2Foracle%2Foracle-database%2F19`
Oracle 12.2 Reference	`https://docs.oracle.com/apps/search/search.jsp?q=%s&book=sqlrf&product=en%2Fdatabase%2Foracle%2Foracle-database%2F12.2`
Oracle Base	`https://www.google.com/search?as_q="%s" site:oracle-base.com`
PL/SQL Developer forum	`https://www.google.com/search?as_q="%s" site:https://forums.allroundautomations.com`
Stack Overflow	`https://stackoverflow.com/search?q=%5Bplsql%5D+or+%5Bplsqldeveloper%5D+or+%5Bsql%5D+and+%5Boracle%5D+%s`
williamrobertson.net	`https://www.williamrobertson.net/#stq=%s&stp=1`
oracle-developer.net	`https://www.google.com/search?as_q="%s" site:oracle-developer.net`
Jonathan Lewis	`https://jonathanlewis.wordpress.com/?s="%s"`

Now you can look up syntax and examples online with a right-click:

That's it

Those are the main menu customisations I use regularly. In part 2 I'll cover Session Browser tabs.