PL/SQL Developer by Allround Automations

PLSQL Developer is an extensible Oracle programming IDE that comes with almost 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. Somehow Allround manages to continue offering this tool for a very reasonable price, so it is a great value if you can't or won't cough up the exorbitant fees for the competition.

This version 4.0.2 came close to getting our highest rating of 5 stars. We expect that Allround will have fixed the minor problems we found and that this will be a 5 star product by early 2002.

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

See the Comparison table which compares the performance of PL/SQL Developer, TOAD, RapidSQL,and other tools Screen Shots typical screen shots ( AA site ) typical screen shots ( Orafaq )

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
Allround must have a UI expert in house. The most unique feature I found was how actions allowable for a certain object, were available anywhere that object could be found.

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, 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.
The Code Assistant (see below)
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.
Right-clicking anywhere within any SQL statement to send it to the Query Builder or Explain Plan window. The SQL statement can be in reverse-engineered code, in source code files, in the Command Window editor, or other PSD windows.

Customization
As mentioned earlier, if something seems amiss, chances are you'll be able to customize this tool to your liking. There are 16 tabs in the Preferences window for this purpose. It even has the ability to customize parts of the main menu, adding favorite macros and links to documents. I find it very compelling to point each developer's install at a centralized repository of macros, templates, project-wide training, Excel templates, 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. I just wish I could customize the icons used for each folder.

Documentation
RapidSQL continues to have the best Help system I've seen (geared toward the IDE). But PSD's interface is intuitive, and the manual is complete. I found I didn't miss the usual F1 Help to guide me through certain dialogs. I gave PSD's Help 5 stars for a different reason: it's Help engine and system geared toward Oracle programming.

PSD really shines with its built in Help library 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 everything Oracle has to throw at you. 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. I can't begin to describe what a pleasant surprise this was. I'm used to using my browser (yet another external tool) to view the Oracle docs. The little search applet never works right. 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 15 seconds or less looking for a match. Wahoo!

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.

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 open at once, but after using PSD's single-connection method, I found myself less confused and not needing to double-check which instance I was connected to all the time.

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. The biggest complaint I had about not being able to indent blocks of code using the more-familiar Tab and Shift+Tab will be fixed for the next release.

Code Assistant
I became dependent on this feature. It is much more handy than I thought it would be (TOAD gave me my first taste, but PSD does it three times better). It even works for your own tables and stored objects, and advanced stuff like object types

Code Contents
This panel in the Program Window worked much better than TOAD's attempt at a similar feature. It is useful for large packages that have lots of functions, procedures and other elements. Among other features, it allows you to easily navigate to a selected sub-portion, or generate a test script just for the packaged element selected.

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 2 of the other major competitors 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.

Testing
Aside from the usual feature 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. 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.

You can right-click on the name of a runnable 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 a parameter that is of a complex datatype, like a user-defined or table-based record, and then generate local variables and assignment statements that handle it. PSD is the best so far, but it declares the datatype as "record," which won't compile or run until you modify it.

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 PL/SQL 2.3 and 8i elements. The manual clearly states that object types and records can't be viewed or modified. Other datatypes, including collections like nested tables of a scalar datatype, work just fine. Bravo!

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
One feature that PSD includes for free, is an easy-to-use interface into the new 8i dbms_profile package. PSD's implementation lets you easily 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. For new Oracle developers not accustomed to reading plans, it has a feature that's worth the entire license cost: the ability to show you the exact flow of steps Oracle will take during execution of the statement. I've seen this feature better implemented in expensive optimizers like Quest's SQLab or Spotlight. But it works well in PSD too, and it's included!

Querying, Data Editing
Although I still feel TOAD's data editing and querying capabilities are stronger overall, PSD is the next best. The fact that it comes in this inexpensive package makes it all the better. It's options for manipulating the visual and ordered display of the result set is more flexible than TOAD's. The ability to export, paste straight into a spreadsheet, query by example, sort, and more, are all solid and gave me no trouble.

SQL*Plus replacement
The Command Window is a godsend. It takes the place of having SQL*Plus open all the time. Thankfully, 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, describing a table. This is what SQL*Plus 8.1.7 on NT does to the describe <.table> 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.

Plug-Ins
Where the default features of PSD fall short, the free plugins, well, plug the holes. I installed the plugins which integrate with our customized copies of RevealNet's PLFormatter, and our installation of Merant's PVCS. Being able to format an entire script or package body per our project standards with a single click is a must due to the productivity gains. I prefer using the PVCS interface myself, but other shops might find the tight integration of the VCS plugin very handy. I also installed the ftp plugin, which is a feature I use a lot from within our team's UltraEdit text editor. Having that within PSD was another reason I was able to forget opening my other tools for 2 months. Assuming the available plugins don't fill your need, if you have someone that can produce a DLL, you can write your own plugin to extend PSD and integrate with whatever you need. The API is straightforward, and the installation routine for your new DLL is easy. For example, you could write a plugin to tie certain VCS actions to update a database in your proprietary change management or project tracking system. Or if you miss having certain DBA abilities, you could write some hooks into some of the free DBA utilities, or make calls out of your DLL to another extensible tool, like Embarcadero's DBArtisan.

Back to top of file

---

Who should use it?

PL/SQL Developer was designed for the Oracle programmer. Although some simple tasks ordinarily owned by DBAs are possible, it is not meant for the DBA. If your job is both programmer and DBA, you will want to either get a DBA-specific tool, or use TOAD with the DBA module instead.

Like TOAD, this tool has so much to it and is so flexible, that it could be a bit overwhelming for beginning developers. However, the tool can be configured to match their needs as well. I found it to be more intuitive and usable "out of the box" than any other I've tried.

Back to top of file

---

Competitive products

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

TOAD - Quest ( Orafaq review )
SQL Navigator - Quest ( Orafaq review )
Rapid SQL - Embarcadero ( Got a good Orafaq review ). Has the advantage of supporting SQL Server, DB2 and Sybase in addition to Oracle.
SQL Programmer 2001 for Oracle - BMC (recently purchased from Sylvain Faust) ( Orafaq review ). Supports Microsoft Transact-SQL, Sybase and Oracle .
ManageIT SQL-Station - CA (Computer Associates) : Supports Lotus Notes, MS Exchange, MQ Series, SAP, PeopleSoft . Works using agents for each supported database
Expediter/SQL - Compuware
Procedure Builder - Oracle: Comes packaged along with Oracle Developer.
Golden, PL Edit, GoldView, GoldLoad - Benthic Software (shareware): Mainly a PL/SQL editor
EZSQL - John Dorlon ( Orafaq review )
Keeptool Includes ER-Diagrammer along with PL/SQL-Debugger
Procedit - OraSoft (one of the only options for Linux, under LGPL) A simple PL/SQL editor.
KORA by Ullrich Wagner, the other alternative for Linux : A PL/SQL editor.

Back to top of file

---

Detailed review

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

Back to top of file

---

Shortcomings

I conducted this review during a medium-complexity re-code and test phase of a custom PL/SQL-based trouble ticket system running through iAS 1.0.2.2 and Oracle 8.1.7. Where that system didn't cover a feature of PL/SQL Developer, I pointed it at some of our other systems that incorporate advanced features of 8i to see how thorough the coverage was. I could not break this tool. I tried hard too.

As one example, I threw a 10 table join SQL cursor at its Query Builder for reverse-engineering. It contained DECODEs, outer joins, multi-nested functions wrapping a column, short-circuited OR logic, etc. It handled the whole thing without so much as a whimper. The only real problem with this tool is its inaccurate DDL, generated when reverse-engineering certain Oracle objects that make use of advanced Oracle 8 and 8i features. DDL that should have shown clauses for partitioning and index-organized table declaration and storage, for example, was missing. If it weren't for this problem, I would have given a full five stars to PSD. This is not a problem for us though, since we keep our DDL in Embarcadero's ERStudio and don't trust other tools to reverse engineer it well. Allround intends to fix this by the next two point releases.

Other than that, these were the only things I could find to nit-pick about:

• I found myself missing stylized icons next to each object in the browser. It seems much easier to navigate a database by icon, rather than the object type name.
• A few keyboard shortcuts that seem standard in most Win32 apps, are missing or different. Ctrl+N and Ctrl+O don't do what you'd expect. Tab and Shift+Tab don t indent/un-indent selected blocks by default (to be fixed in 5.0). Luckily you can customize the keyboard mappings to overcome a few of these.
• The Command Window has a little trouble doing column formatting of result sets. It tends to chop things up and lose characters from the front of lines (fixed in 5.0).
• TOAD's ability to store a history of past SQL statements, no matter what window or connection they were executed in is wonderful. I often need to run the same statement in several instances and several open windows. Or I need to recall a complex statement from yesterday that I didn't think I'd need again and so didn't save. TOAD is a lifesaver in these situations. PSD is the only one that comes close to matching TOAD. Unfortunately, unless I manually save a statement list, once I close a SQL Window, I've lost the history of SQL statements executed there.

Back to top of file

---

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 $150 + $50 for a year of upgrades and technical support. For a tool this fully featured, that's very affordable. The price per seat drops to $90, $75, $50, and $30 as you purchase 5, 10, 20 and 50 license packs respectively. If you actually have a shop that needs more than 50 seats, Allround also offers unlimited licenses for $3000 and the upgrades/support for $1000. 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: www.allroundautomations.nl

Sales Department
Holskottelanden 4
7542 KN Enschede
The Netherlands
sales@allroundautomations.nl

Back to top of file

---

Support

The product has been rock solid. I only contacted their support to verify conclusions I was making for this review. When I did email them, the answers were right the first time and the response time was satisfactory.
Support and upgrades costs $50 per year.

Back to top of file

---

About the Author

Bill Coulam has spent the last 6 years developing custom N-tier web applications for RBOCs & LDCs, always with an Oracle VLDB as the back end. He currently works for New Global Telecom in Golden, CO as a senior Oracle programmer & performance tuner, applications architect and programming coach. You can reach him at bill.coulam@ngt.com .

Can you write reviews and articles like this one? Why not become an editor or reviewer for the Oracle FAQ? Find out how you can make money, get noticed and advance your career click here