Hora Product Review
OraFAQ had me review KeepTool's 5.1 suite back in early 2002: It was good then, but is even better now at 7.2. The primary product is Hora, used for browsing, coding, reporting, monitoring, maintaining, and tuning the database. Separately licensable are the ER Diagrammer and PL/SQL Debugger. Hora also includes some nice extras that allow you to code (in various languages) while disconnected from the database, generate HTML documentation for your schema, and reverse engineer DDL for selected objects or entire schemas.
KeepTool’s engineering team seems deeply familiar with the tasks required of Oracle experts and novices alike. Their products demonstrate they kept both sides of the experience spectrum in mind. Hora, in particular, is thoughtfully designed. Although it sports almost every feature you need, it manages to remain visually simple to use and navigate, dynamically altering the interface with new toolbars, menus and context-sensitive options where appropriate.
Hora should be on your short list of tools to consider. It fulfills the needs of DBAs, developers, data operators, report writers, and power browsers, all at a compelling price point.
History[edit]
Hora, short for Handy Oracle tool, was born in 1996 when three programmers from the German software firm PSI AG decided to wrap all their SQL scripts in a Delphi user interface. It quickly developed a loyal following and they formed KeepTool GbR in 1997, later reformed as KeepTool GmbH in 2000. KeepTool releases a major revision of Hora annually, with significant additions in each, often beating competitors as the first to support new Oracle features. 2006 was no exception when version 7 was released, with even more powerful components, and wizards and interfaces for 10g. Version 7.2 achieved a major functional milestone, nearing the heady space currently occupied by TOAD Professional.
Supported Operating Systems and DB Versions[edit]
Desktop OS: The suite is supported on all 32-bit Windows operating systems.
Connectivity: The suite supports Oracle SQL*Net v2 and higher, as well as 10g Instant Client and direct TCP/IP connections.
Database: The suite supports Oracle Database server 7.2 and higher, including 10g and XE. My evaluations were done on 9.2.0.6, 10.2.0.1 and XE.
Installation[edit]
The products can be licensed and installed as stand-alone desktop clients, or in a “floating license” mode, where N clients can access the product concurrently, maximizing value for those shops where developers work in shifts, or don’t need Hora open all day. There is also the unique option of a disconnected floating license, where a consultant or telecommuter can continue using the suite for up to 30 days disconnected from the network license.
New releases are small and easily downloaded from the web or via FTP. Download size, installed size and RAM requirements are all kept to a minimum, which is a pleasant surprise compared to today’s bloatware.
Installations are simple and straightforward. Stick with the defaults and use the Typical (full) install. Old releases must be uninstalled before new releases, but old settings seem to be retained.
Who should use it?[edit]
There are tools that focus on the Oracle DBA. There are those that cater primarily to PL/SQL developers. Finally, there are many tools aimed at the casual user (front-end programmers, data analysts, report writers and management who infrequently query tables and view stored objects). Hora was designed to meet the needs of all three types of users. The only other tools that accomplish a similar feat are TOAD and SQLdetective.
If you are mainly shopping on behalf of the casual user, KeepTool also provides HoraLight, a slimmed-down €99 version of Hora that offers all its power for importing, exporting, browsing, querying, reporting and editing code.
In short, Hora can be used by all of your knowledge workers who must interact with Oracle in any way. If you have been using inferior products but now need something enterprise-class on a budget, or are fed up with the expensive licensing and support from CA, Quest and Embarcadero, then Hora may be the perfect choice or replacement.
What does it do?[edit]
Considering the features offered by competitors? Nearly everything. Seriously.
See the detailed review below for brief accounts of the compelling features. If you find a feature it does not have, suggest it to support. Their engineering team listens well and is often able to respond to requests by the next release.
What problems does it solve?[edit]
Oracle is a complex piece of software. With a mind-boggling array of objects, features, and syntax to support, display and manage, many tools quickly become overburdened and visually confusing. Hora keeps its head above water and presents all that information in a concise and logical manner. Other than a few special screens, the entire application is one giant, hierarchical database browser. This make it very easy to find anything you need. One of KeepTool’s goals was to make Hora intuitive and useful “out of the box” in the hands of everyone from data entry operators to master DBAs. They accomplish this admirably by presenting basic information up front, and keeping more advanced features just one click away, be it an icon, menu item, right-click or tab.
Many tools have a weak to mediocre visual component for displaying the data in Oracle objects. Hora’s data grids are extremely powerful, providing advanced features like filtering, sorting, grouping, visual rearranging, master-detail browsing, simple exporting to many popular formats, and much more. But it uses these data grids throughout the product, not just when operating on tables and views.
Finally, the ER Diagrammer plugs a hole in the market, which, until recently, was addressed by no other competitor: that is, provide a simple, fast and affordable physical modeling tool. Up until recently you had to pick from really awful freeware/garageware diagrammers that weren’t even worth your time to download, or $3,000-$5,000 full-blown modelers like ERWin and ERStudio. The market just did not offer a sub-$1000 modeling tool that covered all the nuances of Oracle. KeepTool’s ER Diagrammer does a fine job filling that hole, and does so for less than €250.
Detailed Review[edit]
The KeepTool family is composed of three main products: Hora, ER Diagrammer and PL/SQL Debugger. Hora also comes with three small applications, HTML Documentation Generator, Reverse DDL Engineer and SQL Editor. As mentioned by both previous KeepTool reviews, attempting to cover the deep functionality in Hora would require its own book. Observe. Here is a list of the top-level pages available from the Outlook-style sidebar, and their first-level subtabs:
Windows and Tabs[edit]
| Standard | Additional | DBA |
|---|---|---|
|
SQL Tables/Views Data Browser Sequences PL/SQL Package Synonyms Jobs Scheduler Pipes Db Links Local db settings Materialized Views Granted Privileges MView Logs |
Tablespaces Users Sessions Database SGA & Statistics Redo Logs Audit Performance Real Time Graphs: Dictionary Resource Manager |
Schema Java Types Operators Index Types Dimensions Outlines Directories Libraries XML |
Luckily, coverage of so much functionality already exists as found in the user guide, tutorials and videos (videos come with the boxed set).
After finishing this review, [url=http://www.keeptool.com/en/download.php]download[/url] the 30-day trial and give it a spin. Read the user guide, the help docs, and the website newsletters. Since there is no server-side install required (except a special role if you need sensitive DBA features), you shouldn’t need special approval to run the trial on your desktop.
This section will focus on a sample of the most thoughtful features found in Hora, as well as a brief synopsis of the remaining five tools.
Connections[edit]
As of v7.2, KeepTool products finally have the option of saving encrypted passwords. Plus they’ve extended the power of their data grid, so your scores of connections can be viewed by last connection time, server, user, etc. Of course, the usual options are there, as well as the ability to assign a color scheme to sensitive connections, or a customized logo image, or a short comment that is saved to help you remember details about particular environments.
Hora and SQL Editor[edit]
Despite such a dizzying array of wizards and interfaces, Hora seems simple at first glance. After starting it and connecting, you will notice your connection information, database version and session identifiers in the title bar. Going top to bottom, you will next see the main menu, the dynamic toolbar, the tabbed window list, and then the Outlook style sidebar. The window area is to the right of the sidebar. Your cursor will be waiting for your first SQL statement in the SQL Scratchpad window’s editor (more on that later). All of these items (except the main menu) are optional, resizable, and hideable. In the bottom left is a little schema picklist. Change the schema there, and the contents of the active window change accordingly. Assuming your connection has the right privileges, this allows one to quickly jump back and forth between schemas.
After you determine which windows you will use the most, customize the Favorites sidebar, adding your choices to the list. Further, when familiar enough with the sidebar’s icons, you can minimize the sidebar to only show the icons, saving screen real estate.
Before we dive into some of the more nifty windows, one final note on three features under the Extras main menu item, features that prevail across Hora: SQL Preview, SQL Recording and Auto Refresh. These are three independent options that you can set. SQL Preview shows you the SQL that the user interface was about to send to Oracle on your behalf. This is a welcome relief to expert DBAs and developers who know exactly what they want, and are used to doing things by hand. This gives them the option of intercepting the generated SQL statements, and reviewing or modifying them before being run on the database. SQL Recording logs every successful SQL statement run by Hora. This opens up many possibilities, including that of using the recorded statements to capture a script that you can then apply on separate Oracle environments. With Auto Refresh on, the active object window or results pane will be periodically refreshed, handy when watching the contents of a table being accessed concurrently by end users, or monitoring sessions, the SQL area, or transactions.
SQL Scratchpad[edit]
This is generally the first window you will see. SQL Scratchpad and File Explorer navigation trees are on the left, blank SQL editing area in the middle, and a code navigation pane on the right. It works as you’d expect. You write SQL statements in Scratchpad’s editing area. You run them individually or as a script. Substitution and bind variables are supported. Results are shown in a powerful data grid that appears in a new window. When you close Hora, or move to other windows within it, these statements are automatically saved for you. If you run them frequently, you might save them with a name. Over time, you might create and organize a few more folders, called “roots”, under the Scratchpad tree, storing your named statements in them. These folders and statements can be placed on a network drive, so common statements can be shared across a team. If you are one of the data analysts or report writers, that’s probably all you need to know about the window. But that is only scratching the surface. As mentioned before, this seeming simplicity is KeepTool’s strength. If you want to do more, the real fun begins…
Select the File Explorer. This is a component that eliminates the need for you to jump out of Hora to go find a file related to your task. It is also the interface to whatever SCC-compliant tool you are using to version code. But don’t just stop at Oracle-related files. Go ahead and select other files that you use to implement your systems. Binary files are opened in their associated applications, and text files of all kinds are opened inside Hora, nicely highlighted, with a document structure or class/routine navigator pane to the right. It supports file types of SQL, PL/SQL, C, C++, C#, Java, Java Script, HTML, XML, Pascal, Visual Basic, Delphi resources, batch files, Perl, Fortran, PoxPro, Latex, Modula 2, PHP, Oberon and some others. Of course most of these languages would ordinarily be written and compiled in another IDE, but Hora’s editor lets you easily see and edit all these related files in one place. Code folding is another new feature that briefly collapses chunks of code to get a better view of the code’s layout. Folding is available for all file types that Hora recognizes.
Key mappings are configurable. All display and formatting behavior is customizable per file type. Bookmarks, block indentation, column selection mode, and most of the usual editor options are there for efficient Oracle coding. To aid in remembering syntax and routine names, there is Code completion, Code snippets, and drag-n-drop items from the DB Object browser. SQL History and Code Templates are two features that aid in recalling and re-running personal statements and code. From this embedded SQL editor, you can compare files, jump to the visual query builder, run an explain plan for the selected statement (heavily improved for 7.2), start the profiler, describe the object under the cursor, and more.
Then there is the new SyncEdit feature. When you select a block of code, a little double pencil icon appears in the left gutter. Clicking this icon establish a Syncronization range for your selection. If you then make a change, say adding a missing “(i)” array element subscript to a collection variable, the same change will be made to all the other occurrences of the same text within the sync range.
If you need help with an Oracle error number or built-in, just select the word or phrase, right-click, and select Search | Oracle Help… This automatically searches the online Oracle documents and takes you to the search results page.
This same editor component is used throughout Hora when viewing and editing anonymous blocks and stored objects, as well as in the standalone SQL Editor.
Data Grids[edit]
The data grids within Hora use a new component introduced in v7. As before it defaults to Grid View, but you may place it in Record View if that’s easier on the eyes, or in Text View if you need a quick ASCII copy of the results. Change the ordering by clicking column headings. Rearrange the results by dragging and dropping column headings where you want them. Starting with v7, the grids resembles certain OLAP reporting tools and Excel. To see this, check the “in-memory mode” box. For the 401k mutual fund table shown above, I dragged TIER to the shaded bar above the results and the data was redisplayed, with funds now grouped by the distinct values in TIER. I dropped column Morningstar RATING next to TIER and a further subgrouping was applied within each tier. Click the arrow on any column heading, and you can filter the result set by the known unique values (like in Excel) or customize your filter using operators and wildcards. You can also right-click under any numerical column and apply a Sum, Min, Max, Count or Average calculation (see the bottom shaded bar in the image above). This comes in handy, for example, when I’m querying all the segments for a certain object and want to know how many total bytes are involved.
From the data grid (and most Hora windows), you can take the data you are viewing, and print it, take a screenshot from within Hora (File | Hardcopy), dump the results to the SQL Recording log file, immediately copy it to Excel (the best I’ve seen), send it straight to a report that can be saved, printed, or exported to several formats, or send it to a sweet Fast Reports 3.0 designer window, where you can create reports worthy of executive decision makers. Finally, you can also export your data to one of eighteen formats, with each format kicking off an intelligent wizard that gives you high control over the output. The data importing is similarly well optioned, allowing you to import from thirteen different file formats.
Tables/Views[edit]
Tables/Views is such a useful window, it is a shame there isn’t the space to have a screenshot per tab. There are the common and obvious tabs for table columns, constraints, indexes, and partitions. But they aren’t mere regurgitation of what one finds in the data dictionary views. The information is intelligently grouped and labeled, so you can quickly find all the related attributes.
For example, in the Columns tab, there is a little “+” icon next to each column that expands into a tabbed view that shows you all the constraints, indexes, histograms, and so forth that the column participates in. This is superb and saves you a lot of time from manually seeking that info from the data dictionary.
As another example, on the Constraints tab, there is a little column labeled “Index support.” This is not found on USER_CONSTRAINTS for foreign keys. Here Hora has gone to the trouble of finding indexes related to foreign keys for you.
There are also little object-specific features on almost every tab within Hora. Just right-click and poke around the dynamic icons on the toolbar and the header areas above the grids. For example, if you click inside the Contraints tab grid, you will see some really handy options, like creating an index on a non-indexed FK column, or seeing all the exceptions for a disabled or non-validated FK.
Let’s take a look at few other high points.
Partitions. I frequently need to see all my partition range endpoints at a glance. Hora translates and displays the antiquated LONG datatype HIGH_VALUE contents for every partition. Other tools place HIGH_VALUE behind an additional layer, which renders their partition interfaces useless to me.
Access tab. It shows you who is currently tapping into that table, useful in environments with a highly concurrent user base.
Master/Detail. It is a light entity-relationship browser that automatically diagrams related tables. Clicking on an entity switches you to that table.
Privileges. This tab even includes column-level privileges, which most tools ignore.
Content. If the table is related to others, there will be tabs at the bottom of the data grid, which allows rapid navigation to related tables, based on selected rows in the parent. If two tables should be related, but aren’t, you can define that missing relationship using the “Other Table” subtab. Voila, virtual FK on your master-detail tables! Now you can analyze both in unison as if the FK existed.
PL/SQL[edit]
For developers, this and the SQL window will become their home. The Overview tab is further divided into PL/SQL object type tabs. Click one of the type tabs, like Package Body, click the item of interest, and then drill down into its source, privs, synonyms, dependencies or audit trail configuration. The Overview tab alone is worth a mention, being rich in information and actions allowed on PL/SQL objects (see the screenshot below).
In the grid, you can see less-common attributes like how many times it’s been loaded, how big the code is, whether it is natively compiled, and even the new 10g compiler warnings. This is thorough coverage of what I like to know about my packages.
The Source Code tab is another view of the same SQL editor component we observed earlier. Here you can open files from the file system or packages from the database, then edit, compile, compare code, run explain plans, etc.
The Call Interface window generates anonymous PL/SQL blocks to speed up unit testing and debugging. Hora is still one of the best at generating anonymous blocks, especially when dealing with parameters of newer and user-defined datatypes, which most competitors implement poorly.
Dictionary[edit]
It is difficult finding and wading through Oracle’s official data dictionary documentation. When I do access it, I’m usually there only to discover what a certain column means. With 10g, there are so many views you need a tour guide to blaze a path through the forest of objects. Hora provides at least 3 mechanisms to help you here. First is the alphabetical bar attached to the left of the overview list in all windows. Clicking a letter jumps you to the first object that starts with that letter. Second, Hora cleverly groups the related views that start with the same prefix into expandable nodes. Third, if one of the nodes contains hundreds of objects, you can quickly get to your desired view by using Hora’s incremental search feature. You just click inside the column containing the object names and start typing the first few letters. As you type, Hora auto-scrolls through the rows below until you stop typing or it has found the item you were searching for. These features are useful for shops with hundreds or thousands of Oracle objects to manage.
Jobs and Scheduler[edit]
The Jobs window is still top notch and is my tool of choice when administering jobs. Support for the new 10g Scheduler has been added with its own window.
Materialized Views[edit]
As you can see from the subfolders listed above, they don’t merely display the known materialized views. They also provide seven more tabs where you can view and modify important settings and attributes relevant to materialized views. Even the Overview tab is worth studying. In addition to the usual refresh and status attributes, it also show the privileges and initialization parameters pertinent to materialized view creation and tuning. This demonstrates KeepTool’s dedication to “the trenches”, and what is needed by real DBAs to get their jobs done with minimal typing and headache.
Tablespaces[edit]
The Overview tab and its right-click menu are where most tools stop when offering a tablespace feature. Hora dives in further and provides six more tabs that drill down into the tablespaces to show you rich information on the data files that compose them, the individual segments within them, which blocks and files the segments can be found in, fragmentation, etc.
Sessions[edit]
This is another good example of KeepTool thoughtfulness. Other than a few additional features in relation to locks, transactions and long ops, they have pretty much the same features as other tools that provide a session manager. However, as you look at the Overview tab, for example, you once again notice how everything is logically grouped and organized. Vaguely named V$SESSION columns are given more understandable names. And instead of following the same generic presentation model in every grid, KeepTool tweaked this Overview grid such that the Session and Client Application column groups remain fixed, while the contents in between them scrolls horizontally. This is useful for keeping the “who” and “where” front and center while wading through all the session information.
SGA, Performance and Database[edit]
Each tab in each of these windows deserves its own little review, but the depth and breadth of coverage is simply overwhelming for the scope of this review. Check the options for each window and tab above for an idea as to what each is capable of.
From graphically seeing where all your memory is currently at, to monitoring where all your I/O is going, to seeing exactly how each datafile is being used, to polling resource graphs, to formatting and grouping everything in the SQL area by areas of tuner interest, there are scores of useful features and interfaces in these windows to ease and simplify the job of the DBA.
Schema[edit]
We need to clarify something here. There is both a Schema main menu item, as well as a Schema window (found under the Additional toolbar). They both contain powerful and updated features you will need right away.
From the Schema main menu item, you can elect to gather or remove system or schema statistics using the newer DBMS_STATS method, or computer and estimate statistics using the old ANALYZE method. It also offers the global constraints and triggers windows, where you can enable or disable your constraints (filterable by constraint type) and enable, disable or compile your triggers. You can compile the whole schema, clear the whole schema, truncate all tables in the schema, create a large grant script, change your password, or create an Oracle export parameter file (very handy if you can’t remember the syntax and options from day to day).
From the Schema window you can, among standard options, set FGA policies, compile all invalid PL/SQL objects in dependency order, work on schema triggers, or manage the entire Recycle Bin.
XML[edit]
Hora has one of the first interfaces I’ve seen into the new XMLDB portion of Oracle 10g. I’m not qualified to comment on its thoroughness, but it seems the handsome formatting, element browser and views could be quite useful for those that do use Oracle’s XML features.
PL/SQL Debugger[edit]
The debugger has now been improved to where it rivals the power offered by the competition, including breakpoints that can be set dynamically, and display of variable value upon mouse hover. The only thing the debugger still lacks is conditional breakpoints.
Since the code pane is essentially the SQL Editor component, the usual features are all still available within the debugger for any anonymous blocks or debugged code, including sending suspect code to an explain plan or the profiler.
One nice touch is a feature that only one other competitor has, the ability to detect and display DBMS_OUTPUT as it is written by the program being debugged, instead of waiting for the program’s output at the end.
ER Diagrammer[edit]
The ER Diagrammer has improved a great deal. The algorithm they use to read a schema and generate an IDEF1X-compliant, hierarchically arranged ERD is very fast. Even on a huge schema with almost 400 tables, it took less than 2 minutes. It is an intelligent little diagrammer too, attempting to shade related tables in the same color. A fast zoom is provided, as well as a little picklist in the lower left corner that is used to quickly find a given table in a large model. Hovering over a line shows the name of the foreign key, the names of the related tables and the relationship direction in a little tooltip. PK’s are clearly identified. Datatypes are indicated by little icons in the left gutter of each table. And a column’s participation in an alternate key is also indicated. You can create subsets to break up larger models into smaller, more manageable submodels. And you can keep these now independent models up to date by reconnecting and rereading objects from the schema.
Diagrams can be copied to clipboard for inclusion in work deliverables, emails, etc. Or they can be saved as BMP or scalable EMF files, as well as printed and saved as a native DGR file (avoids having to rescan later).
You can create models in this tool, then forward engineer DDL which can be run in SQL*Plus or Hora.
HTML Documentation Generator[edit]
For a quick, simple deliverable in a portable format, this tool does the job. It produces a single HTML file that documents the tables/views and PL/SQL source code in a given schema, creating a hyperlinked table of contents at the top of each section.
The Tables section includes each table’s comment and tablespace, columns and their specifications, constraints, indexes, triggers, child tables, and hyperlinks to any PL/SQL objects that reference the table.
The PL/SQL section shows each object’s source code, as well as hyperlinks to the PL/SQL objects it calls, and the PL/SQL objects that call it.
Reverse DDL Engineer[edit]
As the name implies, with this you reverse engineer the DDL from existing schemas. After connecting, you pick the objects for which you’d like DDL, pick a file destination and Generate. It’s fast, accurate and produces well-ordered, handsome DDL. This is useful for cloning development environments across database server environments, comparing DDL for supposedly similar schemas, etc. The first tab presented after you connect is the Schema tab. The tab offers some control over important items like whether sequences start over or retain their existing setting, whether the storage clause is added for tables and indexes, whether roles and privs are ignored, whether to spool output or compile the schema at the end, etc.
It even handles composite partitioned table DDL well and accurately, something a few highly rated competitors do badly or take too long to parse.
Shortcomings[edit]
- In my opinion, if the debugger option is purchased, it should be an enabled window inside Hora, not a standalone application.
- Given the state of competing tools, the Profiler and HTML Documentation Generator could use refinement.
- The SQL Editor component still does not permit you to work on more than one open file at a time.
- If you customized anything in the anonymous block of the Call Interface tab, and then move focus to some other tab and return, when focus returns to Call Interface, your customizations will be gone. Save your customizations before leaving the tab.
- No SQL or PL/SQL beautifier/formatter. The lexer does a wonderful job recognizing, parsing and displaying the syntax and keywords of most file types, but the formatting that it applies is only virtual. It never touches the formatting of the actual file underneath.
- DB Browser is infinitely better than previous versions, but still has a few issues that will probably be fixed by 7.3.
- Like some competitors, it doesn’t handle ref cursors as parameters or return values, something we use a great deal here.
- For huge models with lots of data integrity constraints, ER Diagrammer tends to cross and hide the hundreds of relationship lines, making the model tricky to navigate, even with their hierarchical layout scheme. It is already a good value at just under €250, but if KeepTool were to introduce one of the other common layout styles, like orthogonal or tree, being careful to auto-bend lines around entities, that would be ideal.
- Finally, probably due to the huge amount of work put into v7, there are still a number of bugs yet to shake out. Hora and the Debugger crashed or froze on me a number of times. But each time I was able to identify the pattern, KeepTool had it fixed within a day or two.
Purchasing Information[edit]
Visit the KeepTool Home Page. The default language is German. Click the "English" link at the top if you don't speak German. Follow the Ordering | Prices link to get the current price list in Euros. As of this review a single license for Hora is €599. The diagrammer is €249 and the Debugger is €189. A boxed version is an additional €25. HoraLight is offered for €99. With currency conversion, possible discounts for bulk and educational customers, as well as floating license configurations, it is best if you contact KeepTool directly.
There is also a unique commission opportunity for those who refer new customers to KeepTool, found here.
About the Author[edit]
Bill has been designing and tuning large, custom Oracle OLTP applications since 1995, and has a soft spot in his heart for anything that makes his work or his teams faster and more productive. Bill has no connections with KeepTool, GmbH.
