Setting up PL/SQL Developer, part 1
Written for PL/SQL Developer 8.0.4 using Oracle 220.127.116.11 and Windows XP.
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
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):
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. 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.
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.
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:
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).
You can do this for most types of object. For example, "View Spec & Body" is handy for packages and types.
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)
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.)
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
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
DBMS_MVIEW is a public synonym for
SYS.DBMS_SNAPSHOT). This picks up all the
supplied packages and types such as
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.
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.
"Ignore unrecognised PL/SQL" is worth checking if you work with source code files that contain non-PL/SQL code such as
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.
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.
Add SQL*Plus to the Tools menu
Go to Tools > Configure Tools, and follow the steps below:
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.
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
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":
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.
Customised toolbar - more useful stuff in less space
Fed up with constantly re-typing
SELECT * FROM, and correcting
SEELCT? Well, your constant re-typing
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:
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
raise_application_error(-[Error number=20000], '[Message]', [Include existing error? = FALSE/TRUE]);
Now try that again:
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
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 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:
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:
If tighter security is required, you can leave out the password and let the login screen prompt you for it.
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.
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?
Hit F1 and find out:
Those are the main menu customisations I use regularly. In part 2 I'll cover Session Browser tabs.