Investigating and Managing Differences with SQL Navigator

Ben Boise — Mon, 23 Nov 2009 08:00:00 GMT

Quest Software has two of the most powerful tools for PL/SQL development: Toad for Oracle and SQL Navigator for Oracle. Personally, I spend more time working with Toad for Oracle and I truly enjoy the solution. Before I proceed any further, I want to clarify a point. I am not advocating one solution over the other for PL/SQL development. That’s a conversation I refuse to have, because it borders on arguing which religion is better. I’m of the opinion that you should use the solution you’re most comfortable using and that helps you get your job done well.

Recently, I went to visit some customers who are using SQL Navigator for Oracle. Well, because I spend most of my time with Toad for Oracle, I found my SQL Navigator skills to be a bit rusty. In preparation for our meetings, I spent some time refamiliarizing myself with this solution. During this preparation, I discovered a feature that I wanted to share with you. Granted, I’m running the risk of exposing a feature you’re already aware of, but that’s the chance I take .

SQL Navigator for Oracle has a utility that allows you to compare both script differences and object differences. You can launch the comparison process in a few ways (imagine that). We’ll focus on using the menus within SQL Navigator for Oracle. All you have to do is select Tools | View Difference:

Selecting this menu loads the following dialog:

As you can see, we have the option of selecting Script or Object. The Script option allows us to compare a variety of file types. If you click the ellipses, you can activate the Files of Type dropdown to see the file formats that are supported for comparison purposes. I’m going to take us through an example of using the Object comparison.

The interface is very straight-forward. Simply select the Object radio button and fill out the proper objects you wish to compare from the various dropdowns. You’ll see something similar to the below screenshot:

**Please note that I am comparing the OW_CUSTOMERS table in my QUEST_DEV schema to the OW_CUSTOMERS table in my QUEST_PROD schema. My connection provides me access to both of these schemas; hence, the Session information is identical in both dropdowns.

You guess it. Now all we do is click the OK button and we have the following results:

You can see that we have a column size difference between these two schemas. This is indicated by the blue syntax in the above screenshot (line #3).

In my opinion, this is where it gets good. If I select the line that is different (line #3), you’ll notice a change in the toolbar:

The eraser icons give us the ability to Delete left text or to Delete right text. The arrow icons allow us to Copy to right or Copy to left. What does this mean to us? Using these icons we can now react to these differences.

Let’s say that we need to make our QUEST_PROD table look like our QUEST_DEV table. Our QUEST_PROD table’s NAME column is 64 bytes and we need it to be 70 bytes. Using the Copy to right arrow (the arrow pointing to the right), we can start to make this change:

Now, line #3 is identical on both the left-hand and right-hand sides of the screen. If you click on the floppy disk icon, on the toolbar, you’ll be prompted to save the changes as a script.

Icon to click:

Dialog to save changes:

Now that we’ve saved our changes, we can very easily load the script into the editor and execute it to apply our changes. All we have to do is click on the SQL icon in the toolbar:

This loads our script into SQL Navigator for Oracle’s editor:

We can now execute this script to apply our desired changes!

Investigating differences is very easy in SQL Navigator for Oracle. Perhaps equally important is the fact that we can react to these differences in a safe and logical manner. I find that I’m sometimes my worst enemy when it comes to managing changes. SQL Navigator for Oracle helps save me from myself.

Ben Boise's Blog

Investigating and Managing Differences with SQL Navigator