Home > Tips & Techniques > Consulting the Oracle

Consulting the Oracle

A look at Oracle, SQL and other tools

John R. Joyce, Ph.D.

Download / Listen to an Audio Intro from John R. Joyce.


When working with Laboratory Informatics systems you are, by default, going to be working with databases. In industry, despite Microsoft's best efforts, this is likely to be some flavor of the Oracle relational data base system.

Most users will, with relief, be restricted to accessing this database only through the application. This both masks the complexity of the system and helps ensure the integrity of the data. Of course, most people would probably stay as far away from dealing with the actual database as they could anyway. However, there will come a time when someone has to peek around the application curtain. If you are the system administrator, this might be necessary to correct corrupted data or to graft on additional functionality that was not engineeed into the original system. Others might need this access to obtain a clearer understanding of how the system works and how the various tables and fields interrelate. This latter reason is particularly true when attempting to write reports to pull data from poorly documented systems (let alone poorly designed ones).

Of course, Oracle itself provides a number of their own tools for this purpose. Perhaps the most widely used one is SQL*Plus. It is a classic command tool that allows you to do some very powerful things with the database. With it, you can create and delete entire databases, modify table structures, and enter/modify data, as well as perform complex data queries and reporting. It actually comes in a number of 'flavors,' the one you are most likely to use with Windows being SQLPLUSW.EXE. However, it is not the easiest tool to use, coming at it cold, so you would probably find it wise to keep a good reference book, such as Jonathan Gennick's Oracle SQL*Plus: THE Definitive Guide, Second Edition, from O'Reilly and Associates [ISBN: 0-596-00746-9, 2004, 582 pp, $39.95] on hand. This is an excellent reference that I find myself using all of the time. If you've grown up with Windows, you may find it amazing what you can do with a few simple text commands!

Oracle has recently enhanced their Oracle SQL Developer tool as well. Version 1.5, released in April of 2008, is a free tool designed to allow graphical database development. This tool can be used on Linux and Mac OSX systems, as well as Windows to access any Oracle database, v.9.2.0.1 or later. Among the features it supports are connection creation, creation and modification of objects, querying and updating of data, running and debugging of PL/SQL scripts, and the copying and comparing of schemas. In additon to Oracle databases, this tool also supports the creation of connections to TimesTen, MySQL, SQL Server, MS Access and Sybase databases. This new release also supports versioning and source code control through integration with the Concurrent Versions System (CVS) and Subversion versioning systems. I have only had a brief opportunity to work with it, but it appears quite capable, with a visually impressive interface. The only issue I've had with it so far has been in creating some of the initial database connections, but that may have been due to the machine on which I was running it. You can find more information on the Oracle Technology Network Web site, as well as in the Oracle Development Tools User Group (ODTUG).

As with many other software applications, perhaps because they were originally designed by enthusiasts, some of the better application tools come from a source other than the application vendor. An excellent example of this is Toad (Tool for Oracle Application Developers), from Quest Software.

Toad is fairly straightforward to use for much of its basic functionality, but it is not what I would consider intuitive. Unless you have someone to mentor you on Toad operation, I think you'll find it beneficial to pick up a copy of Bert Scalzo's and Dan Hotka's Toad Handbook from Developer's Library [ISBN: 0-672-32486-5, 2003, 297pp, $29.99]. Bert is the product architect for Quest Software, so he does have some familiarity wlth the product. Once you've become proficient with it, you might still want to keep a copy of the book Toad Pocket Reference for Oracle, 2nd Edition, by Quest Software's Jeff Smith, Patrick McGrath, and Bert Scalzo, published by O'Reilly and Associates [ISBN: 0-596-00971-2, 2005, 122pp, $9.95], on hand.

Toad is available in a limited functionality Freeware version, as well as a full featured licensed application. Even the Freeware version is packed with useful features and allows you to create and manipulate Oracle databases. It also allows you to easily examine the definitions of tables and views, as well as review the dependencies between tables. This can be done in SQL*Plus as well, but you generally need to have significantly more expert knowledge to do it. Toad's ability to clearly display the database records in a table format, as well as its single record view, make it much easier to reverse engineer the behavior of an application to determine what it is actually doing, in comparison to what it says that it is doing. I personally find that these two tools compliment each other very well and frequently use them together when working on database projects.

Another tool that looks interesting is SQL Edge from Bay Breeze Software, though I have no hands on experience with it. According to their Web site, it too can display database records in a variety of perspectives. The most interesting to me is their Model Perspective. This is supposed to be able to display the database tables, and the relationships between them, in an Entity Relationship (ER) diagram, which you can then print or save. Having a tool that simplifies the generation and printing of an ER can be a real boon when working with relatively undocumented systems.

Of course, a search of the Web will turn up a plethora of other data base tools. Some of these are general purpose multifunction tools, others are utilities designed to solve a single specific problem. Some of these are commercial products, while others are shareware or free.

I've found the many support boards on the Web to be very useful in puzzling out the complexities of Oracle, SQL and other tools that use them, such as Crystal Report Writer. Careful use of Google and other search tools can lead you to many answers on these boards. In those cases where it doesn't, the users of these boards are usually quite enthusiastic in offering their help. At least this is the case for clearly worded queries where you include all of the pertinent detail for them to make a diagnosis. Remember, they are not magicians and you must give them something to work with, but if you do, you'll likely get something useful back. It may not be the answer you desired, but at least it will usually allow you to take the next step.

John Joyce is the LIMS manager for Virginia's State Division of Consolidated Laboratory Services. He may be contacted at editor@ScientificComputing.com.

Reated Resources
Oracle www.oracle.com
Oracle Development Tools User Group (ODTUG) www.odtug.com
Oracle Technology Network
www.oracle.com/technology; www.oracle.com/technology/products/database/sql_developer/fil
Quest Software www.quest.com
Toad for Oracle Freeware www.toadsoft.com
O'Reilly & Associates www.oreilly.com
Developer's Library www.developers-library.com
Bay Breeze Software www.baybreezesoft.com
Experts Exchange www.experts-exchange.com