TOAD ORACLE 25.3 - COMPARE DATA is not working properly

Toad for Oracle & DevOps Toolkit
1

Using COMPARE DATA in 25.1 we have good results. Only the diferences are showed but now in 25.3 we execute the same comparison and we receive all lines as NOT EQUAL and the suggestion is to delete all lines and insert again.

2

Hi Luishmr,

There were very few changes to compare data from 25.1 to 25.3 so I'm not sure what the problem could be. I haven't heard any problem reports from that window. It sounds like maybe 25.3 didn't find a primary key between the tables and 25.1 did. Please check that and let me know.

If that's not it, please provide more info so that I can reproduce this. Screen shots of each step and spool sql output would be good start? Maybe also some table DDL.

If you don't want to post that info for the world to see, you can email it to me: john.dorlon@quest.com

Thanks

3

Hi John,

I have the same schema, same table in 2 different ORACLE instances. 2 different hardware.

Comparing in 25.3

image
image1352×673 27.6 KB

Result are:

image
image1356×648 24.6 KB

Comparing now exact the same instances, hardware and table. BUT NOW IN 25.1

image
image1353×650 22.9 KB

image
image1351×704 26.7 KB

image
image1350×707 25.8 KB

4

TOAD_COMPARE_DATA_TABLE_DEFINITION.txt (67.0 KB)

5

Proposed Sync Script_in_25_3.sql (95.2 KB)

6

Thank you for the details. I can reproduce it and will get to the bottom of it soon.

7

Hi Luishmr,

This problem happens when all of the following are true:

  1. You are comparing Tables, Views, or MViews
  2. The primary key of the table contains a mix of VARCHAR2 and non-varchar2 datatypes.
  3. The tables are in different databases and you are not using a DB Link in the comparison

So, if you already have or can create a DB Link from one of the databases to the other, you can work around it like this:

image
image1039×884 34.4 KB

If you are not able to create a DB Link, and you want to just do a compare, but not a SYNC, you can compare queries instead of tables:

image
image977×743 29.8 KB

This bug was introduced by a mistake that I made while fixing the bug reported here.

If you turn on spool sql, you'll see that we are using this order by, which is correct, to query one of the tables:

Order by CD_RELAT_CONFIG, NLSSORT(NM_LABEL, 'NLS_SORT = BINARY'), NLSSORT(TP_PARAMETRO, 'NLS_SORT = BINARY');

and this order by, which is incorrect, to query the other table:

Order by NLSSORT(CD_RELAT_CONFIG, 'NLS_SORT = BINARY'), NLSSORT(NM_LABEL, 'NLS_SORT = BINARY'), NLSSORT(TP_PARAMETRO, 'NLS_SORT = BINARY');

The different-connection comparison code is expecting these tables to be fetched with the same order by, and this is what's causing the problem.

As you noted the bug exists in Toad 2025 R3, but not 2025 R1. Just FYI, it does not exist in 2025 R2.

This will be fixed in the next beta.

-John

8

Thank you very much John.

Appreciate it. The comparison tool is very useful here in my company.

Luis H>

=-=-=-=-=-=-=-=-=-==

9

You're welcome. I'm sorry that I broke it. :frowning:

I hope you can use the beta at your company so that you don't have to rely on the workarounds. You can get the beta here.