Linker IT Software
Order Home
menubar-top-links menubar-top-rechts
SQL*XL: Database to Excel bridge litLIB: Excel power functions pack encOffice: Protect your Excel file easy and safe
Home FormatterPlus review

Formatter Plus by RevealNet


Gerrit-Jan Linker

Click to enlarge



If you are developing PL/SQL applications you should consider buying Formatter Plus, which provides one click formatting of PL/SQL code. It also provides syntax checking, and reviews your code for correctness, maintainability, efficiency, readability and program structure. It tells you why a line is bad and provides expert recommendations to improve the code, improving your skills! The Code Profiling feature looks at version dependencies, declarations, flow control and so on.

Formatter Plus is a brand new product. However RevealNet's Knowledge base and PL Formatter have been around for some time, and are widely used. This review is based on the first release version 1.0

This program is a "must have" for the beginner and a "nice to have" for the expert. I have spent most of my professional life coding PL/SQL (about 5/6 years) and I really enjoyed using it during the three weeks I evaluated it. And yes, I did pick up some new knowledge on the way!

Usefulness to DBA
Usefulness to developers
Functionality, how much does it do
User interface, intuitive/friendly
Software quality, integrity, robustness
Documentation quality and scope
Technical support availability
Value for money
Ease of integration with other tools and systems
Overall rating

  • Formats PL/SQL code
  • Offline PL/SQL syntax checking
  • Built-in Expert Recommendations to catch common coding mistakes
  • Code Reviewing
    • correctness
    • maintainability
    • efficiency
    • readability
    • program structure
  • Code Profiling
    • Module analysis
    • Oracle version dependencies
    • Declaration analysis
    • Cursor analysis
    • Flow of Control analysis
    • and more...
  • Back to top of file

    What is great about this product

    Formatter Plus is easy to use and very fast. It is nice to have a one button press instantaneous code formatting utility at hand. Everybody dreads reading through old code to see what it does. Formatter makes life a bit easier by at least ensuring the formatting is clear, indenting is correct, etc.

    As the name suggests Formatter Plus is all about formatting PL/SQL plus a little extra. Actually the little extra turns out to be a lot extra. I value the Code Review and Code Profiling more than the formatting! It spots weaknesses in the code and aids to analyse PL/SQL source. A great added value is that it provides tips and it explains why a code line is identified as bad code. Therefore the user improves his coding skills.

    This program is a must have for the beginner and a nice to have for the more experienced. I have spent most of my professional life coding PL/SQL (about 5/6 years) and I really enjoyed using it during the three weeks I evaluated it. And yes, I did pick up some new knowledge on the way!

  • Back to top of file

    Supported Operating Systems and Versions

    You need Windows95 or better
    All versions of PL/SQL (1.0 through to 8.1) are supported
    Oracle SQL release 6.0 through to 8.1 are supported

  • Back to top of file


    The licensed version of the program can be downloaded from RevealNetís web site here. A short form had to be completed and an email promptly dropped in my inbox with the download URL. Downloading the file was painless and both a ftp and a http server were provided for download. Both methods allowed downloads of about 10kB/s and the file to download was just over 3 MB. The installer ran without problems and at the initial run of the program it asked for the license key. From the start of download to running the program for the first time it took me 15 minutes. I took another 30 minutes to download the companion Knowledge Base products: Active PL/SQL Development and Instant Messages. These files are a bit larger and are in total just under 20 MB in size. Blessed with a ADSL connection I was ok but I realise that using a normal 56k modem it could take a whopping 1.5 to 2 hours.

  • Back to top of file

    What it does

    Formatter Plus can be used to format PL/SQL code. More importantly it can be used for syntax checking, code reviewing and code profiling as well. Formatter Plus highlights potential problems and provides background material to back it all up.

    I would recommend any PL/SQL programmer to use this utility to check code before delivering it. A basic check takes no more than 15 minutes, it is fun to do and perhaps best of all, you improve your programming skills while doing it.

  • Back to top of file

    Who should use it?

    Most Oracle PL/SQL developers, all levels of experience.

  • Back to top of file

    Competitive products

    There are no products which compete directly with Formatter Plus. However, some of the functions can be found in other products. A number of companies bundle or interface to RevealNet products, including Quest, Embarcadero and BMC Software.

  • Back to top of file

    Detailed review

    First time use

    Formatterís graphical user interface (GUI) is designed well. A sample project is loaded which invites to one to experiment. After opening a few files in the sample project the concept of projects and file usage is clear. Then the real thing Ė letís format some code! All toolbar buttons have help text so I quickly spotted the Format, Review, Syntax check and Profiler buttons. A little later I found that right clicking in the code provides the functionality to execute these functions as well.

    Having formatted a piece of code I wanted to change the preferences. It obviously doesnít format it exactly to my preferences. The preferences section is well designed and caters for most programming styles and tastes. It does take a little experimentation to optimise it. It is all very easy to do and it's enjoyable too.

    Now the settings are right I try to open some of my own files. This is a little harder to do. Dropping a file onto formatter actually adds it into the project which is not what I wanted to do. Using File|Open to open a file does not include it into the project so thatís what I had to do.

    Finally, experimenting with the "Plus" features Ė reviewing, profiling and syntax checking. There is no learning curves here! It is very intuitive and easy to use. I loaded a package body and performed a syntax check. The output window immediately shows that there are no errors Ė as expected. After reviewing the code the review section of the output window opened up inviting me with colourful icons to explore the different sections of the review. Very handy and intuitive.

    Formatter Plus features

    • Navigation & Organisation of Files

      Formatter Plusí window is divided into a few panes. There is a project browser, an output window and editor tabs. These panes can be resized or hidden as required.

      Project browser:
      The project browser pane contains a list of files in the current project. When you launch the program first Formatter shows a default project. Apparently you have to always have an open project. At present there is no facility to close a project apart from opening another one or creating a new project. RevealNet promises to include the close project option soon.

      Creating a new project is easy. It saves a .mp file on the hard disk but thatís ok. In order to work with files effectively you have to add them to the project. Using files ad-hoc can be done, but drag and drop from the file manager is not supported for ad-hoc files. You can only drop them to include them into a project, not if you want to just use them. With file open you can use a file without adding it to the project.

      Adding files into the project is relatively easy. In a browser window you can pick the files you like to add. A bit annoying is that the program only filtered files by their full 3 letter extension. I wanted to add all s_*.pkb files and s_*.pks files. I could not use s_*.pk? or *.pk? as a filter so I had to first list all pkb files, pick out the ones starting with s_ and do it all over again for the pks files. RevealNet promises to allow wildcards on the extensions soon.

      With the selection of pks and pkb files (the extensions I use for package specifications and package bodies) I wanted to create a sub folder in my project to contain them. I created a folder pkb and pks and asked for the *.pkb and *.pks files to be moved. The folder creation was successful but no files were moved. Dragging them in did not work either so I had to remove the files from the main directory and re-add them into the sub folders. A little bit annoying but I did get the job done in the end.

      The impression of this part of the application is that not too much effort has gone into this yet. Iím sure RevealNet will tidy this part of the application up soon.

      Editor Tabs:
      The editor tabs section actually shows the PL/SQL files. Each open file is represented by a tab at the top of the screen. As with all the screens of this nature it is a little confusing at first. You can use items in the window menu to arrange the screens: tile, cascade, and there is a very handy Close All facility.

      Double clicking on the file in the project loads it into the Editor and creates a tab for it. When you double click the file again it will jump to the corresponding tab in the Editor.

      The Editor is what you expect from a good code editor. It highlights the syntax although it seems not possible to change the settings. Comments appear in green, PL/SQL in blue, strings in red, the remaining code in black. The choice of font is good, the code is not too big and not too small either.

      The integration with the knowledge base works well in the Editor. I highlighted the word BINARY_INTEGER in a variable declaration section of some code and right mouse clicked. In the menu a lot of the items are specific to the whole file but you can also execute Search Knowledge Base. No lengthy searches but promptly a small dialog appeared with a list of topics containing the keyword. I chose to view the BINARY_INTEGER data type which produced a screen full of information on binary integers.

      Output window:
      The output window is the last pane in Formatter Plusí user interface. If you are familiar with MS Outlook you will recognise a lot in this part of the application. Formatter uses the same style menus as outlook does to present the analysis results. There are 3 types of analysis available: Formatting Results, Code Review and Code Profiling. There is more about these techniques later in this review.

      What is nicely done is the way you can drill down into areas of the analysis. As an example I chose to review one of my packages. The code review section in the Output window opened and I could see the different review areas. At one glance I could see the program made 6 remarks about code correctness, made 12 remarks about readability and 12 remarks about the program structure. The program did not raise any remarks about the efficiency or maintainability of my code. Curious what Formatter may have spotted I investigated further.

      To view the individual review items you can navigate through a tree list. In the code correctness section I saw that all 6 remarks fell into 1 category. Formatter warns me that zero length strings are not equivalent to a NULL in Oracle8. When I open this list item I see the 6 lines of code in which the error was spotted. Clicking on the code line loads it into the editor. Now what should I do? Help is available when right clicking on the item: Code Review Tip: It explains that according to the ANSI SQL standard empty strings are not the same as NULLs but that Oracle PL/SQL treats them as NULLs at the moment. It recommends to change the code as Oracle may one day fully comply with the ANSI SQL standards.

      At this point I can only conclude one thing, this piece of software is extremely useful and a must have to developers at any level of experience.

    • Code Formatting

      To format a file is easy to do. A toolbar button is provided to start the formatting but right clicking in the PL/SQL code also offer the Format Text command. The formatting is done according to preferences you set. Formatting is a difficult subject since there are probably as many formatting styles as there are programmers. Luckily Formatter Plus can cater for most styles and techniques. To show you how it works I will format a select statement according to my preferences.

      I want to format the following (unformatted) select statement:

      select sysdate,decode(to_char(sysdate,'D'),'1','monday','2','tuesday','not monday or tuesday')

      First I will be using the default formatting settings. I press the formatting button and Formatter Plus formats it to:

      '1', 'monday',
      '2', 'tuesday',
      'not monday or tuesday'

      Not bad!
      Next, I will try to apply my formatting preferences in the preferences dialog in Formatter. I like the built-in functions to be lower case so I set Case | Built-ins to Lowercase. Secondly, I do not like that the FROM keyword is indented so I apply the setting to not indent it: Specific Statements | Select = Keywords Left Aligned. Now the statement looks like this:

      SELECT sysdate,
      decode (to_char (sysdate, 'D'),
      '1', 'monday',
      '2', 'tuesday',
      'not monday or tuesday'
      FROM dual

      The last thing I do not like about the formatting is the way the commas are used. I like commas to be placed before the items and not at the end. I set Lists | Commas = Leading with spaces. The resulting format is:

      SELECT sysdate
      , decode (to_char (sysdate, 'D')
      , '1', 'monday'
      , '2', 'tuesday'
      , 'not monday or tuesday'

      This format is pretty close to the way I usually format it. When I use my formatting preference I would code it like this:

      SELECT sysdate
      decode (to_char (sysdate, 'D')
      ,'1' , 'monday'
      ,'2' , 'tuesday'
      ,'not monday or tuesday'

      You can see that I am not very far away from achieving my overall goal. Perhaps playing with one or two other formatting options I could get me even closer to my exact preferences.

      The formatting process is very fast. Formatting a single document is almost instantaneous. The only problem I have with this functionality is with formatting a single page. It does not preserve your position in the file. After formatting, it always jumps to line 1 so I have to scroll all the way down again.

      When formatting multiple documents things are obviously a little slower. To test it Iíve loaded 75 packages (total code size = 1.8 MB) into the project. Formatting all these documents took about 10 seconds and Iím not on even on a Pentium III so the speed of the formatter is excellent.
    • Code Review

      To review a piece of PL/SQL code is very easy to do and it is only a mouse click away at any time. Pressing the toolbar button will review the current PL/SQL document showing the results immediately in the Output Window. I used a 1200 line PL/SQL package body and the results appeared instantaneously. To review multiple files, select multiple files in the project and right click to select the Review command. Reviewing the same 1.8 MB of code used in the Formatting exercise took an amazing 15 seconds only.

      The review output is presented in 5 categories: Code Correctness, Maintainability, Efficiency, Readability, Program Structure. I will mention a few examples in each category below.

      Code Correctness:
      1. Use of Char. In 10 code lines, Formatter informs me, I use declaration of CHAR variables. This could potentially be a problem. The knowledge base gives the background: CHAR variables take up to 10,000 characters in PL/SQL whereas a CHAR column in a table can only take 255 characters.

      2. Test on cursor should follow the cursor operation immediately. In 2 lines in my module I refer to row%NOTFOUND a few lines after I fetched the row from the cursor. The knowledge base tells me that this is not good practise to insert code lines between the fetch and the test of the cursor. Agreed.

      1. Fetch into a list of variables instead of a record. Apart from less typing it is indeed a better practise to fetch them into a record instead.

      2. Cursor references an external variable (use a parameter). It is a good coding practise to use parameters in cursors and not use external variables.

      1. Number has no precision.

      2. Varchar2 declaration with length greater than 500 characters. The knowledge base warns that Oracle will always allocate memory of the full size of the varchar even if only a few characters are used.

      1. End of program unit, package or type is not labelled. It is good practise to end a procedure not by just the END; but to include the procedure name as a label and type: END my_proc_name;

      2. Nested LOOPs should all be labelled. For readability nested loops should all be labelled.

      Program Structure:
      1. Function with more than 1 RETURN statement in the executable section. This could potentially be very confusing and is easially missed by someone altering the code later.

      2. Mode of parameter is not specified with IN parameter. The knowledge base explains that it is good practise to always be explicit in the mode of parameters.

      It is very easy to analyse code with the Code Review facility. It provides very valuable hints and tips although some tips and hints may prove not applicable to your specific circumstances. I found a few bugs in my PL/SQL while playing with Formatter and I will definitely keep using it in the future.

      I would recommend coders to always check their code with the Code Reviewer. It takes only 15 minutes to review a module and it adds a lot of quality to the code. In addition to spotting weaknesses in the code the programmer will get more aware of programming issues. It is therefore a good tool for coders to keep their knowledge current.
    • Syntax Checking

      To check the syntax of a module is again very easy to do and only a mouse click away on the toolbar at any time. I checked the syntax in the 75 test modules (1.8 MB of code) and that finished in an amazing 5 seconds. This is fast!

      If errors are found it is a bit disappointing that only the first error is shown. If there are e.g. 5 errors Formatter will only show the first one. Therefore you have to fix the first error before being able to find the second one, etc., etc. It would have been nice to have an error stack where all the errors are shown at the same time.

      When an error is found the code line is highlighted and in the output window the error message is shown. It is a pity that Formatter does not show the Oracle error message codes, the ones we all got used to. The description of the error message is clear and concise though.
    • Code Profiling

      The Code Profiler produces a list of statistics about the module. Again, the process of executing this function is really easy to do. Click on the toolbar button to generate the code profile for the current PL/SQL module. The process is very quick. A single file is done in a second. My batch of 75 packages were processed in about 15 seconds. Speed wise Formatter is excellent!

      The profile of the code is presented in a dozen categories:
      Module Analysis, Oracle version dependencies, Declaration Analysis, DML Analysis, Cursor Analysis, Flow of Control Analysis, Procedure / Function Analysis, Exception Handling Analysis, Built-ins Utilized, Built-in Packages Utilized and more...

      While experimenting with this facility I spotted a few interesting items in the output categories:

      Oracle Version Dependencies shows the lines of code that are specific to a certain Oracle version. It e.g. picked up my PL/SQL tables as being specific to version 2.2.

      One of the subcategories in Cursor Analysis is Cursor References To External Variables. This is bad coding practise and makes modules hard to maintain. Formatter highlights the exact row where external variables are used.

  • Back to top of file


    I reviewed version 1.0 of Formatter Plus before the actual release date (02-April-2001) of the product. I did find a few minor issues in the graphical user interface (GUI) but nothing too serious. The development team at RevealNet were quick to respond to my findings and promised that they would fix some of the shortcomings before the main release of the product.

    The shortcoming I found is the concept of projects in Formatter. It is not worked out very well. Adding files and organising files in the project was a disappointment. To be honest I do not understand why Formatter needs the concepts of projects and archives. The thing that puzzled me is that it is not possible to close a project suggesting that a project is always needed. Most users will use Formatter initially to just open a file for ad-hoc formatting and reviewing. To my opinion the program should focus on this primarily. During my 3 week trial I never felt that I needed projects & archives.

    In general the Oracle related functionality works extremely well. The GUI is there to support this functionality. A little extra work on this side would benefit the product.

  • Back to top of file

    Cost and where to buy

    Download a 30 day free trial here

    Formatter Plus costs $199 per named user.
    For the optional "Knowledge Base" products you have to pay an extra $295

    Formatter is available from 2-April-2001 from RevealNet ( and itís resellers.

    • RevealNet, Inc.
    • PO Box 5560
    • Rockville, MD 20855
    • Tel: 1.800.738.3254
    • Int'l Tel: 301.947.7744

  • Back to top of file


    The price of Formatter Plus includes 1 year of maintenance and support.

    To test the support I wrote an email to their support desk (I found the email address in the About dialog available in the Help menu of Formatter). Within a few hours I received a reply, very good! A very helpful and friendly message pointing out a workaround for my problem.

  • Back to top of file

    About the Author

    Gerrit-Jan Linker is the author of SQL*XL the Oracle to Excel utility ( ). Gerrit-Jan is a freelance Oracle developer in de UK working through Linker IT Software Limited. He has worked over 5 years with Oracle in different environments and on different platforms. Apart from writing SQL*XL Gerrit-Jan has co-authored the Oracle8i DBA bible. He can be reached at

Copyright 2001 Oracle Underground FAQ -
For further reviews of software tools useful for Oracle developers and DBA please visit