PL/SQL Developer by Allround Automations

Index

10-May-2005
Author: Bill Coulam

Summary

PLSQL Developer is an extensible Oracle programming IDE that comes with every feature you'll need "out of the box" to accomplish your daily programming tasks. No longer will you need to keep other editors or Oracle tools, telnet windows or SQL*Plus sessions open to get your job done. Once you skim the complete manual, you'll find yourself more productive than you ever thought possible. It is highly customizable to fit the way you're accustomed to thinking and working. Despite the long overdue price-hike in January of 2005, somehow Allround continues to offer this tool for a very reasonable price, so it is a great value if you can't cough up the exorbitant fees for the competition.

Version 6 gets our highest rating of 5 stars.

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

What is great about this product?

Two general themes come to mind when attempting to summarize what's great: value and flexibility.

It includes an enormous feature set for the miniscule per-seat cost, features that cost hundreds more to add as modules to competing tools.

Most of the time, I found working within PLSQL Developer (hereafter referred to as PSD) to be a dream. It simply worked the way my mind naturally works. In the rare cases I wanted it to do something different, there was always some preference, template, plugin or other such customization that Allround had already anticipated in the design to accommodate me.

Here's a list of other unique and excellent elements:

  • The action you need when and where you need it, all integrated and available from every window. I cant stress this point enough since so many other programming IDEs have disappointed so heavily in this area, constraining you to how they envisioned your work habits.
  • The very best PL/SQL debugger on the market.
  • Powerful editor including goodies like regular expressions, column-mode editing and very easy Macro recorder
  • Complete and context-sensitive help, integrated with Oracle documentation and Oracle Error Messages.
  • Fast, intelligent and customizable context-sensitive auto-completion, known as Code Assistant.
  • Extensible, shareable templates and built-in code beautifier.
  • Error stack stepping, opening each piece of code as you walk through an error stack.
  • Explain Plan GUI and its ability to step through the plan in the proper order.
  • Built-in wizards and visual interfaces for profiling, tracing, building queries visually, importing/exporting, loading data from files, creating test data, and more.
  • Built-in DBMS Pipe and Alert event monitor
  • Full-featured Session GUI allowing kills, tracing, lock and SQL monitoring, statistics, explains from current SQL, etc.
  • Auto-detection and re-compilation of invalid objects in the appropriate order.
  • Code copied out of tool into docs and emails is in RTF format, so syntax highlighting remains.
  • Custom or Oracle Role-based Security model to prevent users from certain features.
  • Very flexible and full-featured data grid. Exports and email to a variety of formats. Automatically copy the contents to Excel. Edit only subsets of large tables. Visually rearrange column order. Drive up and down relationship hierarchies and more.

History

A Dutch software company, founded in 1989, Allround is dedicated to user-friendly software that performs well. They have created software that range from administrative to real-time process control applications. Creation of PL/SQL Developer started in 1997, when they recognized the huge gap between Delphi/C++ IDEs on the market and the near total lack of anything similar for PL/SQL. They felt there were no tools that took PL/SQL development seriously, offering the productivity to which most programmers are accustomed. Through the years the tool has kept its focus on PL/SQL developer's needs and a great price/performance ratio.

The original review of PSD 4.0.2 was published August 10, 2001. PSD has gone though two point releases since then, adding various goodies to its feature-rich bag. Version 6.0 was release August 10, 2004.

System Requirements

Client OS: 32-bit Windows
Client Specs: 15MB RAM, 30 MB disk
DB Version: Oracle 7.3 and up (including 10g)

Installation

You can download the software here. The PL/SQL Developer trial version is fully functional and has all the features you'll find in the registered version, it is only limited by a trial period of 30 days.

Installation took about 20 seconds. It worked the way I expected it to. The installer detected the fact that I have multiple Oracle homes on my desktop and let me know where I could change it in the preferences if I didn't like the choice it made. Make sure you have Oracle's networking software (SQL*Net and up) already installed and properly configured.

What it does

Allround had the noble goal of allowing a developer to perform all day-to-day Oracle programming tasks within PSD. Unlike other tools I've reviewed that had the same goal, I'm happy to report Allround succeeded. I've been evaluating new and upgraded PL/SQL IDE's since June 1997, and this is the first time that I finally find myself spending all my time in one tool. Somebody pinch me; have I died and gone to heaven?!!

Point-of-Need Actions

The most unique feature I found was how actions allowable for a certain object, were available anywhere that object could be found or referenced. Allround must have a usability expert in house.

PSD doesn't follow the rest of the pack by isolating certain actions to certain windows. For example, in PSD one can do numerous things with a table. Among them are obtaining the table's properties, describing the table and querying the table. Of course, these actions show up when you right-click a table in the browser pane, but that's where most PL/SQL IDE's stop. In PSD, no matter where that table name shows up, right-clicking the name will give you the same options. It doesn't matter if the table name is in the results for an explain plan, the output of a Command Window query, inside a column comment, as part of a variable's %TYPE declaration, or random text in a blank editor. If your current connection can see that table, either as owner or through a synonym, it recognizes the text as an Oracle object and presents you with the all the possible actions you could want on that object, when you want it, and where you want it. I found this sped up my productivity considerably.

Here are some other manifestations of this mentality:

Database object hyperlinks

Using the Ctrl key, one can drill down to the original source code where a type, variable, table/view, or function/procedure are declared. Read the manual to fully understand this; you'll immediately grasp how much time this can save you over using the database browser for all navigation.

SQL statement recognition

Another example is right-clicking anywhere within any SQL statement to send it to the Query Builder or Explain Plan window. Again, other tools would constrain you where you can get to the Explain window, but in PSD, it can happen from anywhere a SQL statement appears.

The Code Assistant

Most tools now have a feature they call Code Assistant or Code Insight. PSD provides this functionality wherever you can type, not just in the editor windows dictated like other tools.

Code Contents

With all this jumping around and drilling, The Code Contents panel kindly remembers where you've been, much like a browser, allowing you to go forward and backward, or jump to wherever in a stored program.

Browser

The database browser in PSD is fairly standard. The plain vanilla folders at the top level are a little ugly, but object-specific icons are used for everything below the top level. The browser is, like the rest of the tool, highly configurable. Folders can be color coded, sorted as you wish, eliminated from view, and filtered by any predicate you can code against USER_OBJECTS. You can even create your own folders and hierarchies of objects within them.

Unfortunately, the browser isnt dockable, so when it is visible and expanded, it requires a good portion of screen real estate needed by the editing windows. Hopefully this will be fixed by the next update.

Customization

As mentioned earlier, if something seems amiss, chances are you'll be able to customize this tool to your liking. There are 31 tabs in the Preferences window for this purpose. Any key shortcuts you are used to from previous tools can be mapped here to your liking. It even has the ability to customize parts of the main menu, adding favorite macros and links to documents. That last point is useful for any team development. You can point each developer's install at a centralized repository of preferences, macros, templates, project-wide training and process or standards documents. This is a feature I've desired in a PL/SQL IDE since '97, but have never seen any get it right, until now.

The database browser is customizable as well. Various default or custom filters can be applied, which is useful in environments with hundreds or thousands of Oracle objects. What's more, the browser tracks the items you've used most recently to simplify your life a little more. For some odd reason, after 5 years of waiting, I still cant customize the icons used for each folder at the top level of the browser, but otherwise Im pleased with the browser.

Finally, there are 25 plugins created by Allround and independent PSD-lovers to extend PSD. The most useful in my mind are those that extend the Browser with your own favorite queries and commands, plsqldoc which generates HTML documentation from PL/SQL code, Quick ER for ERD diagramming, the version control interface, and the FTP interface. If you cannot find what you need, you can build your own plugin using any tool that can create DLLs.

Team Development

I believe PSD is the best tool on the market for development teams. Teams share code. Teams work concurrently on the same project. Teams share standards, templates and processes. Often Oracle tools get in the way of these requirements, but PSD accommodates them.

PSD supports Project-based development. This allows a developer to be shared across multiple projects without them going insane from all the pieces and tangents they are juggling.

PSD has LAN-shareable Templates. It comes with a built-in set, but its real power is when you add your own templates that conform to project or company standards, as well as frequently used bits of code unique to your team or project. PSD used to be the only tool that had the foresight to make templates shareable; I am not aware if the competition has caught up yet.

There is a Documents main menu item that comes blank. To it you may add links to local, LAN or Internet-based sites and files. For example, links to the companys portal, to the teams status page, to the projects Gantt chart, to the AskTom website, to the teams PL/SQL Coding Standards doc, and so on. This allows a simple centralization of all the sites and docs you as an Oracle developer need to get your job done.

PSD can be installed locally or the network. This allows for easy team-wide or company-wide updates when Allround releases new features.

For developers that jump between teams with different standards and practices, PSD now has the ability to save and load multiple versions of the entire set of preferences and options. These are called Preference sets. There is even the ability to create a default preference set that the entire team can inherit so they dont have to manually (and problematically) go through 31 Preference tabs in an attempt to get in sync with one another. The time saved by this feature alone and the problems it avoids pays for PSD itself: immediate ROI.

PSD includes a powerful database object comparison tool. I didnt mention it in the previous review. But a senior developer commented to me on how much trouble hed had using similar features in competing tools, so I had to give it a mention here. This feature is fairly standard, but clearly valuable in a team environment where multiple developers are playing in the same space and possibly overwriting code, compiling the wrong version, etc.

With the version control plugin, PSD includes the standard hooks into any SCC-compliant tool like VSS, PVCS and MKS. This is a must for any development team.

If there are user-favorite or project-specific tools that accomplish something with PL/SQL that PSD does not do, you may configure any number of user-defined tools along with icons, shortcuts and placeholders in a toolbar, pointing to the executable and passing parameters. For example, you could pass the code yourre working on in PSDs editor to a code analyzer, or straight to SQL*Plus for execution.

A nice feature added in version 6 is the To-Do list, allowing you to leave breadcrumbs as you code that can be automatically searched and cataloged by the tool, turning it into a To-Do list for each piece of code in your application.

Finally, a personal favorite, PSD comes with a built-in, configurable Code Beautifier. It has had this since version 5. There are still two minor things that Id like to change, but overall, it shaves hours off development time where coding standards are in place, and beats the competition in value, competition that charges $150 to $200 for the add-on.

Documentation

PSD's three help interfaces are ratherhelpful. The PSD-centric help is intuitive, and the user manual is complete. The Oracle-centric help is what got the Help area a 5 star rating. PSD really shines with its built-in Oracle documentation engine. You point PSD at a local or LAN-based install of Oracle documentation. A few minutes elapse and PSD has now found, indexed and categorized every Oracle doc. You check off the books you want to include in your context-sensitive library. Now you can manually search all these Oracle books, or, when you highlight any word in any PSD window and hit F1, the books are automatically searched for you. I can't begin to describe what a pleasant surprise this was. I'm used to using my web browser (yet another external tool) to view the Oracle docs. The little search applet never worked right (the 9i version is much better.) And it seems I'm usually searching for 15 minutes before I find what I want. With PSD's Help integrated with the Oracle docs, I now find that I spend about 5-30 seconds or less looking for a match. Wahoo!

As far as code documentation goes, please refer to the very slick plsqldoc plugin freely downloadable from the plugins page. It can generate passable HTML-based documentation from your existing PL/SQL code or database objects. If you make use of its syntax (resembles JavaDoc somewhat), it produces documentation that easily passes the scrutiny of the most obsessive old-school project manager.

Templates

Not only is Allround the first vendor to finally match the features I've desired in templates, but it far surpassed my expectations. It comes with a complete set of common templates for PL/SQL objects and frequent code constructs. I can replace them at will, create my own types and organize them in new folders, centralize a set of them on the LAN for my team or the whole company, and use the built-in, powerful template syntax. If you make use of the built-in syntax, using a template will pop up a dialog prompting for inputs. You can make some of the inputs conditional or populated from known OS or DB metadata. You can even make the inputs into a limited or open combo-box, filled with data obtained by a hard-coded list or from a database query. This is a very powerful feature I have not seen in any competing tool.

Connection manager

It seems a small thing, but PSD's connection manager is the easiest to use and administer I've seen yet. It just makes my time within the tool a breeze as I fly between Oracle instances and schemas. Sometimes I found that I missed the ability to have multiple connections to multiple databases open concurrently, but after using PSD's single connected database method for a month, I found myself a big fan of this approach.

Being able to connect to multiple instances of Oracle at once is usually the only complaint I hear about this tool. They are thinking about adding the feature. However, I have found that it has reduced the number of accidents Ive had. When I used to use other tools, about once a week Id run a script or compile an object on an instance where it didnt belong. Once I started using PSD, after the initial grumbling, I found that I enjoyed not shooting myself in the foot anymore.

Finally, let me clarify that PSD does handle multiple concurrent connections, just not to more than one database. This allows the tool to have multiple windows querying, compiling, monitoring sessions, and tracing all at the same time. I remember in older versions of competing tools that only allowed one session per database, once I kicked off a long-running query or process, the whole tool was unusable until the query was over, forcing me to open a new instance to kill it. That never happens in PSD. Im able to do all my Oracle work in one instance of one tool. Thats beautiful.

Editing

For an in-house editor, it is quite good. No glaring holes or problems like I've encountered in the more expensive competitor's tools. Any previous complaints I had with version 4 have long since been fixed. Theyve added more powerful regular expression-based search/replace, bookmarks, block and statement highlighting, line numbers, column mode editing and more.

New to version 6, I believe, is the Large Data Editor. Some Oracle shops use Oracle to store all sorts of unstructured data in LONG, LONG RAW, BLOB, CLOB, BFILE and XMLTTYPE columns. For those shops, when you attempt to read the contents of these columns, rather than giving up like most of the competitors, it analyzes the content and will show the content in the appropriate editor in the Large Data Editor window. Images show in the image viewer/editor tab. RTF shows in the RTF editor tab, HTML in the HTML editor tab, plain text in the text editor tab, XML in the XML editor tab, unrecognized in the text editor or hex editor tab. If it is a BFILE, it can be viewed by the appropriate external application which you can configure after reading the column. Each of the above editors has their own unique and useful features. Who knows, perhaps the XML editor tab alone will save you from having to purchase an overblown 3rd party tool if all you do is a little XML editing, formatting and parsing.

Code Assistant

This feature will automatically complete code for you. Type in user_ and a drop-down list of all known Oracle USER* views is presented for you to pick the one you meant to type or find. Type in the name of a table or alias to a table in a SQL statement, and the possible columns are presented for you to pick. Type in the name of a local record variable, and the records attributes are presented. Type in the name of a user-defined, or built-in procedure or function, and it reminds you what the next expected parameter(s) is. Once unique to TOAD and PSD, this feature has become standard among all the competitors.

Code Contents

This panel in the Program Window is fairly standard, but solid. It allows you to quickly visualize and navigate even the most bloated PL/SQL routine or package. Most of the competition has caught up to PSD now, but it used to be the best. With Back and Forward buttons, it works somewhat like a web browser or Adobe Reader. Items and routines within a package can be sorted in order of appearance or alphabetically. From this pane, if you highlight a routine, you can comment the whole thing out/in, describe it, and automatically generate an anonymous block for testing it.

Compiling

Of all the PL/SQL IDEs I've used to compile DB objects and source code, this one is the best. I appreciate how my cursor is immediately placed at the correct point of error (something major competitors used to fail at), and if I double-click the short error message in the error stack, I'm transported to the cause and action section for that error in the Oracle Error Messages documentation. I no longer have to open up a telnet to the Unix box on which Oracle resides, just to run oerr in order to see the same information, which often turns out to be blank or useless, especially if it's a PLS error.

Two versions back they added the ability to do a small code review for you as well, warning you of certain common mistakes, like declaring variables and parameters that are never used or not returning anything from a function. This is similar to the extra-cost features in Quests formatting add-on and results in much cleaner code without much effort.

Testing

Aside from the usual features allowing one to visually execute a procedure or function, the test script feature of PSD is particularly valuable in that it allows you to save a test script as is, including the values you entered for bind variables, output, and more. This enables a developer to save entire suites of unit tests that can be recalled and re-run at any time for bug fixing or regression testing. This is a huge time-saver for developers that know they should, or are forced to test, but dont ever seem to have the time to write them due to all the busy-work. This feature eliminates much of that.

You can right-click on the name of an executable PL/SQL object from pretty much anywhere in the tool, and have it generate a near-perfect anonymous block for testing purposes. I say near-perfect, because no PL/SQL IDE has yet shown me the ability to correctly recognize and handle a parameter that is of a complex datatype, like a user-defined or table-based record.

Finally, from the same test script window, one can jump into the debugger, view the test runs statistics, profile, trace and dbms_output, as well as the HTML output if these are PL/SQL Web Toolkit routines. The test window is a one-stop shop for unit testing, debugging and optimization.

Debugging

PSD sports the usual debugging abilities, breakpoints and rich conditional breakpoints, step over/out/into, variable watches, etc. But it sets itself apart from other PL/SQL debuggers I've used by being intuitive and solid. Unlike others I've used, I encountered no bugs or awful workarounds when putting it through my suite of test packages, which contain, among other things, ref cursors, nested tables, records, tables of records, and other more advanced 8i and 9i elements. During the debug session, I can even view the contents of individual elements of collections, hover over any variable to see its contents, etc. To my knowledge, this is still the best debugger 4 years after the first review.

The debugging facilities also have a "Run to Exception" feature. This is particularly nice in that it pauses the program before throwing the exception, allowing you to inspect the current state of the variables involved in the problem. On top of that, another useful rarity I found was the ability to attach a message to each breakpoint. These messages show up in the output generated by the program run, eliminating hours of tedious dbms_output.put_line insertions just to track where your execution went (for those of you that still do it the hard way).

Optimizing

Once the pioneer, PSD still freely includes an easy-to-use interface for the dbms_profile package (8i and up). PSD's implementation lets you see which parts of your code are hogging execution time, quickly narrowing down the problem areas.

For the price, it has an excellent Explain Plan facility, but not the best Ive seen. As usual, it is configurable and the plans are easily saved, printed and exported. Many tools botch the explain plans for SQL found in PL/SQL code that use PL/SQL variables in their predicates. When this happens, youll get ORA-00904 invalid identifier and end up having to place the bind variable identifier : in front of every variable in order for the Explain to work. PSD anticipates these problems and prefaces the troublesome variables for you. Its little touches like this that add up to hours of saved time per week when using PSD as compared to other tools.

Querying, Data Editing

When I was first instructed to find a replacement for TOAD back in 99, one of the two must requirements was that it mimicked TOADs excellent data editing and querying capabilities. This was especially important for large tables where we just needed to see and edit a subset of rows in the table, and exporting to Excel. Most of the tools fell woefully short. PSD was closest and a very good choice. The fact that it comes in this inexpensive package is icing on the cake. It's options for manipulating the visual and ordered display of the result set was more flexible than TOAD's. The ability to export, paste straight into a spreadsheet, query by example, sort, and more, are solid and gave me no trouble.

The execution ability of the Command of the SQL windows have full support for substitution and bind variables. This will be very important to anyone leaving the SQL*Plus-only world.

The automatically stored library of recent SQL statements, once unique to TOAD and PSD, is still so handy that no Oracle tool should be without it. You may configure it to store as many past SQL statements as you wish.

SQL*Plus replacement

The Command Window is a godsend. It takes the place of having SQL*Plus open all the time. What you are used to doing in SQL*Plus call all be done inside the Command Window. Thankfully, on first use, it knew where all my custom SQL scripts were at and would execute them effortlessly, just as if I were working in SQL*Plus. There are a few things it even does better than current versions of SQL*Plus. For example, remembering and editing prior statements and blocks, and even something as mundane as describing a table. This is what SQL*Plus 8.1.7 on my old NT desktop does to the describe command:

This is what the output from the Command Window looks like:

As with the rest of PSD, this feature is integrated with the other tools as well. It supports most common SQL*Plus commands, but in addition, supports some powerful commands like QUERY[DATA], EDITD[ATA], EXPORT[DATA], EDIT, TEST, VIEW, etc. These commands take you straight from the SQL*Plus-like Command Window to a nice GUI allowing you (respectively) to: view, edit, or export table contents; work on a named PL/SQL source; immediately test a PL/SQL object with some auto-generated anonymous block code; or view PSD's tabbed dialog showing the complete create/edit dialog for a named object.

The little icons and codes on the bottom indicate (and allow you to change with a double-click) which SQL*Plus settings are one, as well as allow one to step through a script.

The Command Window also keeps a buffer of all recently executed statements and can be recalled by arrowing up or down, just like in the command-line versions of SQL*Plus.

DBA Features

PSD comes with a full-featured Sessions window as seen here. It has saved the team a number of times when the Senior physical DBA wasnt around to tackle the tough problems. It is seen here:

Quickly viewing the active SQL, locks, statistics, and automatically refreshing are some of the handier features it sports.

PSD also comes with competent GUI wizards for creating and editing tables, sequences, synonyms, libraries, directories, jobs, queues, queue tables, users, roles, profiles and tablespaces.

Features for the Casual User and Novice

If you buy PSD for the casual data entry operator, college hireling, and occasional executive that wants to look at tables and code, PSD comes with various features that allow the program to be dumbed down and locked up so that the novice cant, for example, modify code, get to production, and get confused or lost.

Additionally, PSD offers visual query building (like Access), linked queries (automatically finds and traverses relational hierarchies), custom and default feature authorizations, read-only mode, and a reporting engine and free viewer worth the entire price of PSD alone.

Finally, despite packing in so many features, PSD manages to maintain a clean, simple (some evaluators called it old school) interface. Some evaluators called it old school. Personally, I find the uncluttered interface a refreshing break.

Who should use it?

Everyone that has anything to do with viewing or editing an Oracle database. I don't say this lightly. I've been reviewing PL/SQL IDE's professionally and casually since 1997. PL/SQL Developer was designed for the novic, the hard-core Oracle programmer, and everyone in between.

A caveat: If you are a hard-core DBA that spends most of your day doing DBA tasks, PSD is probably not the best choice except for the PL/SQL programming you do. You will want to use Oracle's OEM or look into an expensive DBA-centric tool like DBArtisan as well.

I found it to be more intuitive and usable "out of the box" than any other I've ever tried.

Competitive products

See the comparison table, which compares PL/SQL Developer with other SQL development tools.

For a full list of Oracle DBA and development tools, see tools directory.

Detailed review

Please see the comparison table for the detailed review of this product.

Some Screen Shots.

Shortcomings

Ive now been using PSD for 5 years and consider that Ive put it through every test and flaming hoop I can think of. Ive had a grand total of 3 crashes in that period, all of them right after the major 6.0.0 was released. As with all X.0 releases, there were a few glitches. These bugs have since been ironed out in the quickly delivered upgrades.

But even when it crashes, it does so more robustly and elegantly than any tool I've used, ranking up there with Embarcaderos error-handling gracefulness. When I restarted PSD, there were all my unsaved files, waiting to be saved and recovered! Again, that feature alone saved me 4 hours of rework and completely paid back the price of PSD.

The only genuine problem I can find is that it still doesnt support composite partitioning, generating incorrect DDL when reverse engineering the table. This should have been handled back before 9i was released, so is long overdue for the
fix.

The few other nitpicks I managed to find in version 4 have been fixed. I see no reason why PL/SQL Developer shouldnt earn the second 5 start rating that Orafaq has awarded (TOAD being the first). For the price, it should get 5 and stars.

Cost and where to buy

Fully functional, 30-day time-trial downloads of all Allround products can be found here.

A single seat of PL/SQL Developer retails for $180 + $60 for a year of upgrades and technical support. For a tool this fully featured, that's very affordable. The price per seat drops to $108, $90, $60, $42, and $30 as you purchase 5, 10, 20, 50 and 100 license packs respectively. If you actually have a shop that needs more than 50 seats, Allround also offers unlimited licenses for $6000 and the upgrades/support for $2000. If you detest electronic manuals, you may also get the CD and printed manual shipped from their HQ for another $50. They offer ordering by card, check, direct deposit or PO (+$20 for PO option). You may order at: http://www.allroundautomations.com

Sales Department
PO Box 40014
7504 RA, Enschede
THE NETHERLANDS

E-Mail:

Support

The product has been rock solid. When I have emailed them, the responses were usually within a few hours.

About the Author

Bill Coulam has spent the last 10 years developing custom N-tier web applications for telecom and energy, always with an Oracle VLDB as the back end. He currently works for Structure Consulting Group in Houston, TX as the Data Architect. You can reach him at .

»