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 such as Browser Extender you can add your own 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 and Browser Extender items in a separate document.

Fonts and Colours

Fonts

This isn't really anything to do with PL/SQL Developer, but in Windows XP I like to set font smoothing. You can do this from Control Panel > Appearance and Themes > Display > Appearance > Effects: (hover the mouse over the screenshot to see the difference this makes):

Setting font smoothing in Windows XP

This softens the curves of screen fonts, although at the cost of some sharpness. Some people find the smoothing effect just makes the fonts blurry, but I prefer it. Give it a try anyway.

Back in PL/SQL Developer, you can change any of the fonts used from Tools > Preferences > User Interface. I find Corbel or Tahoma gives the browser and grid a friendlier feel than the default Microsoft Sans Serif. I also find I can drop a font size or two from the default 10pt without losing readability. (Note that a smaller grid font lets you fit more information on the screen.)

For editing code, I've never much liked Courier (New or otherwise), and instead I use Consolas. If you don't have it (or want to try some alternatives), you can download Microsoft Powerpoint Viewer or Microsoft Reader, which install some additional fonts including Consolas and the Lucida range. In the screenshot below I've set the browser, grid and main font to Tahoma and used Consolas (in black) for the editor, dropping the size to 8pt. Hover the mouse over the image to see the changes.

Tools > Preferences > User Interface > Fonts

The "Include font style with Copy" option only applies to data grid contents by the way, in case you assumed (as I did) that it applied to code copied from the editor.

The "Main font" option changes the font used in dialogs such as the Preferences screen, and for option tabs. I changed this to Tahoma.

Browser Groups

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, Views and Materialized Views (things you query) 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 Libraries, Java Sources or Queues for now, 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.)

The browser shows all objects by default. 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.

Move the mouse over the screenshot to see the changes:

<Browser folders>

Browser Settings

Double-click on a table name in the browser. By default, the browser node gets expanded to the next level so you can select from Columns, Primary Key, Unique Keys, and so on. I prefer it to show me the table's data (in PL/SQL Developer terms, that means executing a "select * from ..." query in a new window) - after all, I can always click on the browser node if I want to expand it.

Go to Tools > Preferences > User Interface > Browser, and notice the Object Type / Doubleclick Action box. Select 'Table' and 'Query Data' (or you might prefer 'Edit Data' if you don't mind it including the rowid in every query).

<Changing the default doubleclick action for a database object type>

You can do this for most types of object. For example, "View Spec & Body" is handy for packages and types.

Editor Settings

Syntax Highlighting

Continuing our look at style-related settings, I find the default bright red (below) a bit distracting for comments and prefer a more neutral green, and I like literal text and numbers to stand out instead (and be the same as each other). I do go through phases here, but my colour setup is generally something like this: (as usual, hover the mouse over the screenshot to see the edited version)

<Editor colours>

Notice that DBMS_JOB.SUBMIT is highlighted, thanks to the handy Customized Syntax Highlighting plug-in you can download from the Allround Automations website. (Like all PL/SQL Developer plug-ins, exit from PL/SQL Developer before running the installer. Configure it from Tools > Configure Plug-ins.)

Configuring the handy Customized Syntax Highlighting plug-in

I've created two rules: "Standard functions" (which I've assigned to style "Keywords" under the "General" tab) is defined using a query that finds all the functions in package STANDARD, thus picking up the more obscure functions like TO_BINARY_DOUBLE along with the familiar TO_NUMBER etc:

SELECT DISTINCT procedure_name
FROM   all_procedures p
WHERE  p.owner = 'SYS'
AND    p.object_name = 'STANDARD'

"Supplied packages" (style "Custom") also queries ALL_PROCEDURES, but it joins to ALL_SYNONYMS in order to return only packages that have public synonyms, as well as returning the synonym name rather than the actual package name, as sometimes they are different (for example DBMS_MVIEW is a public synonym for SYS.DBMS_SNAPSHOT). This picks up all the supplied packages and types such as DBMS_OUTPUT, DBMS_JOB and so on:

SELECT DISTINCT synonym_name || '.' || procedure_name
FROM   all_procedures p, all_synonyms s
WHERE  p.owner = 'SYS'
AND    s.owner = 'PUBLIC'
AND    s.table_name = p.object_name
AND    p.object_name != 'STANDARD'
AND    p.object_name NOT LIKE '%$%'
AND    p.procedure_name NOT LIKE ' %'

Other Editor settings

User Interface > Code Assistant

"Coding Style" controls automatic uppercase and lowercase effects. The default setting "Smart" may catch you out, as it did me - it seems it's a little too smart for its own good (see this Allround Automations forum discussion). To get language keywords in uppercase and everything else in lowercase, set "Code Assistant > Coding Style" to Lowercase, uncheck "Use original case if possible", and under Syntax Highlighting set Keyword case = "Uppercase". (Unfortunately the Beautifier and Code Assistant tools are not synchronised, so you need to set your upper/lowercase preference there as well.)

Editor: Indent, tabs and wrapping

Without getting into a debate about whether tabs are good or evil, I'll just say that I like them in general, but since no programmers' text editor supports them for SQL I've given up the battle for tabs in PL/SQL. (PL/SQL Developer claims to support them and has a couple of tab-related features, but like all editors it actually cocks them up and it is better to disable them.) "Smart tab" and "Tab size" have nothing to do with the tab character, but "Smart fill" does ("The editor will replace spaces by tabs if possible" - obviously nobody in their right mind would want this). The following settings give you a smart-ish 4-character indent and no tab characters. While I'm on this screen, I find I can print around 111 characters on an A4 page in 8pt, so it's worth setting the "Visible margin" as a reminder.

Indent/Tabs/Wrapping settings

Settings for each window type

If you're used to 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. The SQL window has a tabbed output grid for query results, and handles collections, cursors, CLOBs, even RTF and XML if you're that way inclined. The Report window provides fancier HTML formatting and control break layout (a separate report viewer is also available as a free download), the Program window is for creating stored PL/SQL packages, procedures, functions and types, and (my favourite) the Test window is for anonymous PL/SQL blocks, and provides bind variables and a debugging interface. It's worth checking the preference settings for each window type.

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.

SQL windows

By default, one screenful of rows will be displayed in the result grid, with the size varying according to the amount of space that happens to be available. I find it much more useful to set a fixed number of rows, say 500 (set "Records per Page" to Fixed, 500). Then when you execute a query and you see '500 rows' in the status bar, it's a reminder that there are probably more rows to fetch. (The green "Fetch next/last page" arrow icons will also be lit up of course, but I find I look at the status bar first.)

I find "Auto-select statement" saves a lot of trouble. Without it, pressing F8 will execute all queries on the page unless you select one explicitly by highlighting it with the mouse (or shift-arrow keys). With Auto-select on, it will use the statement under the cursor - although bear in mind that if you have multiple statements on the page they will need to be separated by semicolons, otherwise the selection will include more than one and give you a syntax error.

Tools setup

Add SQL*Plus to the Tools menu

Go to Tools > Configure Tools, and follow the steps below:

1: Define a new tool 2: Enter the path to the executable, the connect string and the Start In folder 3: Set it to appear in the Tools menu (or wherever you prefer) 4: Assign it a button and an icon (sqlplus.bmp is supplied with PL/SQL Developer)

You can now start a SQL*Plus session for the currently connected user by simply hitting a handy toolbar icon. By default it will be a small black and white terminal with no copy & paste hot keys, but that is easily fixed by following the steps in my handy SQL*Plus setup guide. Follow the same process to add your favourite text editor.

Keyboard shortcuts

In PL/SQL Developer, any item in any menu 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? The ones I always set up straight away are Alt-S for "New SQL Window" and Alt-T for "New Test Window", and while I'm at it Alt-P, Alt-R and Alt-X for Program, Report and Explain Plan windows and Ctrl-Enter for "Tools > Code Assistant":

Handy hot key assignments

Handy hot key assignments

Handy hot key assignments

Customising the Toolbar

When I tried using PL/SQL Developer unmodified for a while to see what customisations I missed most, one thing that drove me crazy was the lack of a way to switch the case of selected text (like Shift-F3 in MS Word). It may be a trivial thing and I'm not even sure why I seem to need it so often, but PL/SQL Developer has a handy function to do this buried in the Edit menu (it's Edit > Selection > Lowercase, in case you missed it). The three toolbar icons that go with this are not displayed by default, and instead the valuable real estate of the button bar is taken up with stuff I don't need, like Copy, Paste (we have Ctrl-C and Ctrl-V for those), Print, Tile and "Compile Invalid Objects". It's worth going through the available toolbar icons to see which ones you don't need and which you do - right-click in the toolbar area for the "Customize..." option.

Default toolbar

New (window or database object) Open file Save file Print portrait Print landscape Undo Redo Cut Copy Paste Search/Replace Repeat last find Navigate back Navigate forward PL/SQL Beautifier Selection indent Selection unindent Selection comment Selection uncomment Macro record Macro playback Macro library Cascade windows Tile windows Log on Execute (F8) Break Commit Roll back Explain Plan Query Builder Find database objects Recompile invalid objects Oracle documentation Configuring the toolbar

Customised toolbar - more useful stuff in less space

Log on Sessions Save file Execute (F8) Break Commit Roll back Find database objects PL/SQL Beautifier Uppercase selection Lowercase selection Apply syntax case Selection indent Selection indent Selection comment Selection uncomment Navigation back Navigation forward Code contents Code folding Show special characters Color marker SQL*Plus Configuring the toolbar

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 "Tools > Template List" is checked), and right-click to modify it. Edit the contents, adding the highlighted text after '[Message]':

raise_application_error(-[Error number=20000], '[Message]', [Include existing error? = FALSE/TRUE]);

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);   

Setting up database connections

Fixed users

Here's a thing you'll probably do a zillion times a day: log into the database. Perhaps you have several different development, test and production instances and you need to flip between them. Setting up some frequently used connect strings can save you a lot of time.

From the Logon menu item (the <Login icon> key icon on the left of the toolbar), notice the "Configure..." option. This takes you to the "Tools > Preferences > Oracle > Logon history" screen, the relevant section of which looks like this:

<Configuring database connections>

Use the ">" symbol to define labels for groups of connections, e.g. "Development", "Test", "Production" (or maybe the labels could stand for different applications you need to connect to). Once you've set them up, the login menu looks like this:

<Picking a login from the dropdown list>

If tighter security is required, you can leave out the password and let the login screen prompt you for it.

Session Mode

By default, each new window is in its own session. Although this 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, which might cause problems in some environments, and 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 browser panel and session monitor, and one for all other windows.

Help

If you download the Oracle documentation from OTN (currently a 388.53MB zipfile for 11g), PL/SQL Developer's Help function (F1 key) will look up 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?

<Highlight the text and hit F1...>

Hit F1 and find out:

<Quickly search the Oracle documentation>

That's it

Those are the main menu customisations I use regularly. In parts 2 and 3 I'll cover Session Browser tabs and Browser Extender add-ons.

Bookmark and Share

© William Robertson 2011 www.williamrobertson.net

 Subscribe to articles  Subscribe to code and scripts


Footnotes

1:
3: