Comparing procedures with case sensitive option on results in "No more differences" found

We are working with multiple users on the same procedures and some of us have different toad settings/formatting.

Whenever we compare we like to see what the changes are with our production server.

In comparing with the case sensitive option on it gives some procedures that are different, but when trying to see the differences, Toad gives the message “No more differences” found.

How can I make sure that Toad shows me differences?

In the side-by-side Compare Files window, if you click the last toolbar button, it will bring up the options. I think you might have “Format source before comparing” checked. Uncheck it.

Hi John,

thanks for replying.

The option is not checked.

When checked I would suspect that it formats the code on both sides like I defined the formatting, resulting in the same output on both sides and so no differences?

When unchecked it should show me that there are differences (which it does now) but when actually comparing, no differences are shown.

Below I've added my options as it's easier to show the selected options.

Screenshot_1.png

Kind regards.

If the "Format before comparison" in your screeenshot is checked, then (assuming the only diffs are formatting), no diffs would be found and the schema compare would not identify the procedure as "different".

But if that option were unchecked, and "format source before comparison" in the file viewer is checked (below), then the schema compare would identify the difference, but the side-by-side viewer would not.

I think you need to uncheck option shown below. That won't unformat the currently displayed pair of procedures, but the next time the window opens. it won't format them and will show the differences.

Hi,

thanks for the reply and it seems like a reasonable answer which I like check.

As it is not available in my version of Toad (12.9.0.71) it must be new feature, which I found on https://support.quest.com/technical-documents/toad-for-oracle/12.11/release-notes#TOPIC-741057

I'll update Toad to the latest version and check if succesful.

Kind regards.

Oh, 12.9. Hm, you’re right, that option isn’t there, but if you go to that options window I showed in my screen shot, there is an option in the “Matching” section about case sensitivity.

No success so far.

I did a clean installation of Toad, did not copy user settings from previous installation and unchecked both “Format source before comparing” and “Ignore Case when Comparing Text” in the Merge Options menu.

It still gives me the same output when comparing and when I show the difference details the popup “No more diffences found” shows.

When I then copy over the output script and play it in the other dabase, and do another compare there is no more difference… Which is very strange, because that means there was actually a difference in the first place…

Kind regards.

Hi,

I’m sure it must be some kind of formatting option that gives us this result.

  • Copying the procedure to notepad results in no difference.

  • I’ve tried with a 3rd party product and there I could see the only difference in the two procedures is the word “procedure” in the “create or replace piece”. The first one is “procedure” and the second one is “PROCEDURE”. So Toad does not see this difference with current configuration settings. Still no idea what to change.

Kind regards.

I did fix a bug recently where the “no more differences” message was shown when there was only one difference in the object being compared. It sounds like you are experiencing the same bug.

It should be fixed in Toad version 12.12, which is due out soon. (Sorry, I don’t know the exact date)

Hi John,

ok, thank you for the info.

I’ll keep an eye out for the update.

Kind regards.

Hi John,

I’ve installed the latest update of Toad 12.12 and did another test in comparing the procedure.
It still shows the same procedure in the difference list, and when trying to compare the message “No more differences” does not show anymore. So it does still recognize their is a difference.
But it does not show what’s different… It’s just the same on both sides…

In Oracle SQL Developer the procedures show like this

create or replace PROCEDURE “NAME” is

create or replace procedure “NAME” is

Kind regards.

Hi Jorley.

We must be doing something different, because I cannot reproduce this. I want to fix this but I am not sure what I need to fix. Would you mind writing up some steps so that I can follow exactly what you are doing and reproduce the problem?

Thanks

-John

For me, if the following option is unchecked, then a procedure that only has a difference of upper/lowercase, does not show up in the results. And if it is checked, then the procedure does show up.

If you uncheck “case sensitive” here, you might want to also uncheck “format before comparison”, because that could be changing case on reserved words such as PROCEDURE.

4621.Compare.jpeg

Hi John,

when disabling the option “Case sensitive”, it does no longer show in the list. But neither does any other text that has different case, like simple text (varchar)… So not really a good workaround.

I assume when comparing the differences are found, but when showing not… Are the headers (create or replace) maybe regenerated by the script and thus alike or is it really copied from my code?

Kind regards

Oh, yes, I see what you mean now.

Toad compares everything beginning with the word “PROCEDURE” (since that is where the code starts in DBA_SOURCE), but when Toad builds the script for the side by side viewer, “PROCEDURE” is always in uppercase. The reason this happens is that sometimes we have to change the schema name in the script, so we just remove everything before the procedure name and then put it back in as appropriate.

I’ll make a change to preserve the original case of that word.

Hi John,

thank you.

I’ll try again when the update is available.

Kind regards,

Hi John,

do you have any more information concerning the formatting? Will it be implemented in the following update, and if so, any idea when the next update is due?

Kind regards.

This should be fixed in the current beta. You can go here to get it:

http://www.toadworld.com/products/toad-for-oracle/toad_for_oracle_beta_program/

Keep your 12.12 installed so you can use both (in fact, the beta won’t run without the most recent production version installed)

Let me know if you still have any problems in the beta.

-John

Hi John,

just installed the beta and at first it does not show any difference. It does say their are formatting differences it wants to show. After clicking yes, the difference in “lower case/upper case” does show. This way we are finally able to see the difference. Playing the script does also correct the difference, where it did not in the previous build.

Many thanks for this change/update !

With this update the comparing “columns” and “column comments” options has also been a huge improvement. We do notice that when comparing the column options the line “enable row movement” does no longer show as a difference. Any changes made on that?

Kind regards.

Remember there are two “format” options - one for the schema compare itself, and one for the side by side viewer. You may want to turn them both off.

In Schema Compare, it’s on the “Choose Options” step. Click ‘Options -> Objects’ on the left, and then on the right, under “PL/SQL Objects and View Source”, uncheck “Format before Comparison”.

In the side by side viewer, click the last toolbar icon, then in the dialog that appears, Options -> General on the left, and uncheck “format source before comparing” on the right.

There is an option to control the comparison of row movement.

In the Schema Compare window, on the “Choose Options” step, click on “Storage” on the left, and then make sure “Row Movement” is checked. It’s the 2nd to the last item in the list.