One of the more important tasks in the process of rolling out  incremental developments to a multi-server production system is to double-check that all of the planned modifications, and nothing else, have been deployed. An Oracle expert, Bruce Armstrong, comes across SQL Compare for Oracle, and sees if it helps with this time-consuming task.

A few years ago, I was a contractor at a large aerospace company where I often had to migrate Oracle database modifications between instances (e.g. development to test, test to production, and so on). Promoting code to production for a major release was always done on weekends to avoid extended downtime for the users. My job was to ensure that all the modifications were correctly promoted to production, as this clearly determined the success or failure of the rollout of the new database version. However, the application was large, and modifications were often extensive, so it often meant spending more of my weekend in the office than I would have liked.

Today I’m a development manager, responsible for an application that is deployed to numerous customers at numerous locations. The DBAs within our development shop are responsible for the promotion of code to the testing environment, and eventually packaging it up to send to the customer for production deployment by the DBAs in the field, who are responsible for that process. Even though I no longer have direct responsibility for code promotions, my group is the one that ends up with the bug reports (either internally from our QA department, or externally from our customers) if some code modification didn’t get applied correctly to a database.

In all of these cases, I badly needed an easy-to-use, accurate, and reasonably-priced schema comparison tool, but I just couldn’t find one. I've tried a number of schema comparison tools over the years and, in general, I would have told you that you could have filled any two of those three requirements. You could have easy-to-use and reasonably-priced, but with results that still left you doing a lot of manual effort. Or you could have easy-to-use, with accurate results, but you’d have to be willing to pay a significant price for it.

In this article, I'll review a few of the schema compare tools I'd tried before, and highlight why I thought they fell short of what was needed, and then review Red Gate's Schema Compare for Oracle, and explain why I think it gets the closest yet to offering all three requirements.

Deficiencies of Previous Schema Comparison Tools

Of the tools I've tried over the years, some were essentially free, such as the Schema Compare Tool for Oracle from www.codeproject.com and the schema compare provided in Oracle’s own SQL Developer. Others were built-in as a feature of general purpose third party PL/SQL development tools such as PL/SQL Developer, and some were high-end special purpose tools like CAST Release Builder.

Some of these tools (particularly the free ones) will report that there are differences in objects, but provide little or no details about what those differences actually are. It doesn’t help me much to report that there are 3 lines of code which are different between two packages without showing me what those differences are. In many cases, the differences may be of no consequence for the purpose of the comparison (e.g., the case of an object name or the current value of a sequence). Which leads me to my next point: these tools also don’t provide any capability to indicate beforehand what types of differences I might want to ignore, or the ability to compare the code differences side by side so that I can evaluate them. The result of such a schema compare is often a lot of extra manual effort, attempting to weed out the significant differences from the trivial ones.

Admittedly, the tools that are reasonably priced often do display the code side by side, with differences highlighted once they have been detected. However, without the option to indicate in advance what types of differences are inconsequential, the end result can still be a great deal of manual effort comparing objects, only to find that the majority of the differences are of no interest. This can be particularly tedious if the code difference display is modal, requiring a dialog to be opened and then closed again as each different object is reviewed.

Naturally, the high end products don’t have these problems, but their cost is often out of reach for all but the Fortune 1000.

Schema Compare for Oracle

Red Gate, if you don’t know already, provides the market-leading Schema Comparison tool for SQL Server (as well as a number of other SQL Server tools) and they’re also responsible for the ongoing development of .NET Reflector. Schema Compare for Oracle is their first foray into providing tools to support the Oracle database.

Figure 1 - Project Definition Dialog

When you first launch Schema Compare for Oracle, you are presented with the dialog you use to define your ‘project’ (see Figure 1) – including which two databases you’ll be comparing. Note that you can create a ‘snapshot’ of a database and then use that instead of a live connection for one or both databases. This allows you to prepare a snapshot at one location, and send it to another location so that you don’t have to connect to both databases at the same time to do the comparison. For example, you might have a reference production database in your development shop, from which you can send out snapshots to remote locations, where the field DBAs can do the comparison locally to ensure their databases are consistent with the reference. Note that you can also select which schemas you want to include in the comparison, allowing you to compare more than one schema in the same project.

The second tab of the project creation dialog (Figure 2) is one of the key features that sets Schema Compare for Oracle apart from other tools. The ‘Ignore options’ gives you a great deal of fine tuning capability over what kinds of differences you want to exclude from the initial results - no more hunting though hundreds of trivial differences in objects looking for significant ones.

Figure 2 - Comparison Options

Once you hit the OK button on the project definition dialog, Schema Compare begins the first stage of its work. A progress window is displayed, letting you know what the tool is doing and allowing you to cancel the action if necessary (e.g. you realized that you specified one of the project options incorrectly).

When the analysis is complete, the main window of the tool shows the results; the top half of the window shows a list of the database objects, broken out into four categories:

• Objects that appear in both databases with one or more differences
• Objects that only appear in the source database
• Objects that only appear in the target database
• Objects that appear in both database and have no differences

Each of those categories can be expanded to show the objects included. Clicking on a particular object in the list displays the source from each database in the bottom half of the window, with the differences between the objects (if they exist) highlighted. The ‘Prior’/‘Next’ buttons on the source code view portion of the window allow you to navigate quickly to the next difference between the objects, and those buttons are quite handy if you have a really large object with only a few differences.

Figure 3 - Results Window

The checkboxes between the lists of object names allow you to select one or more objects that you want to perform a database migration for. The ‘Synchronization Wizard’ button becomes enabled once at least one object has been selected for migration. That wizard then walks you through the steps necessary to either create a migration script or apply the changes directly to the target database.

Something we haven’t covered yet is one of the other areas where Schema Compare for Oracle really shines. Remember that source code view section in the main window? Well, it’s not a fixed part of that window - it’s a dockable pane that can be floated free of the main window entirely. In Figure 4 I’ve done just that, positioning the source code differences pane side by side with the main window. The important thing is that as I click on different objects in the main window, the source code review window is updated with the changes for that object: no more opening and closing a dialog between windows. One of the other pieces of software mentioned earlier provides a default “differences display” tool, and then allows you to specify a different tool if you so desire. The result, however, is the requirement for the modal window behavior. I’d much rather give that option up for the ability to navigate through the objects without having to close the source code review window again and again, which is the perk Schema Compare for Oracle gives me.

Figure 4 - Side by Side Windows

Some Caveats

Obviously, I’m impressed with the product. However, as with any product, there is always room for improvement. Here are a few things I’d like to see added:

• A skip unchanged” option in the script comparison pane. Such an option would keep some of the surrounding text for a difference, and would allow the skipped text to be shown. We use an online peer code review tool that provides such an option, and it’s quite useful for focusing on exactly what the differences are (see Figure 5, which is a sample from that tool, not Schema Compare for Oracle).

  

  Figure 5

• A bit more refinement in how differences are displayed. Currently, one color is used for highlighting in both scripts, which just indicates “there is a difference here.” Indicators in the border (either an arrow or X mark) indicate if the change is an insertion or deletion of text, yet no indicator in the border would indicate a modification to the line of text. That works, but using different colors for highlighting (one for additions, one for deletions and one for modifications) might make it a bit easier to see what the differences are at a glance.
• An ignore option for the object difference detection that would ignore 'difference in case' for object names. There is such an option for case difference in PL/SQL blocks, but it doesn’t yet apply to the section of the script that contains the object names. Often, when I’m doing compares between databases, I’m getting false differences reported because the source code in one database has:
  
  
  CREATE OR REPLACE PACKAGE “SCHEMA”.package_name
  
  And another will have:
  
  
  CREATE OR REPLACE PACKAGE “SCHEMA”.”PACKAGE_NAME”
  
  I don’t know why the source code is different there, but the difference is immaterial and I don’t want to see it reported if it’s the only change between objects. Both statements have the same effect. The only time when the case of an object name would be an issue if is lower case was used within quotes; in that case, Oracle does create it with a lower case name. On the other hand, when the object name is not included in quotes, the object is created with an upper case name, and it would match any object with an upper case name in quotes.
• Handling of additional source types. In particular, we do a limited amount of work with java in the database, but there’s no option in Schema Compare for Oracle to handle comparison of java code deployed there.
• Handling of wrapped objects; this last one is a bit tricky. We wrap (Oracle’s term for code obscuration) some of our code when deploying it to production, and Schema Compare for Oracle actually does a good job of handling the wrapped code when it’s wrapped in both databases. You can’t tell what the differences are, but you know that they exist. The issue is when one of the databases has unwrapped code (e.g., a development database) and the other is wrapped (e.g., a production database). I certainly don’t want Schema Compare for Oracle figuring out how to unwrap the code! Yet what I would like to see Schema Compare for Oracle do is (a) detect when this situation occurs, (b) extract the code from the database where it is unwrapped, (c) use the Oracle wrap utility to wrap the code and then (d) compare the extracted and wrapped code with the wrapped code in the other database. When it did that, it would also need to account for the capability for both packages and types to wrap just one portion (e.g., the body and not the spec). Currently, Schema Compare for Oracle treats the spec and body of packages and types as a single block of script.
• The ability to compare database data. This isn’t something I need very often, but Red Gate does have comparison tools for Microsoft SQL Server that compare both schemas and data. I do believe there is a need for such capability, and would like to see it provided for Oracle as well.

Conclusion

If you’re looking for an easy to use, reasonably priced schema comparison tool for Oracle that allows you to fine tune the kinds of differences you’re interested in seeing, then you need to take a look at Schema Compare for Oracle from Red Gate.