Null fields not getting compared using using data compare option of Toad for Oracle

I’m using data compare option of Toad for Oracle to compare the data across two databases.

Data compare seems to be working fine where there is data in all the field but when one of the fields is having null data.

Is there any way of handling the null fields as well (something like nvl)?

I created some differences with nulls by running this script:

create table EMP1 as select * from scott.emp;
create table emp2 as select * from scott.emp;
update emp2 set job=null where deptno = 10;
update emp2 set sal=null where deptno = 20;
commit;

then I compared EMP1 vs EMP2.

Differences were highlighted as shown below. and after syncronization, no more differences were found. Can you give me some more details so that I can reproduce your situation?

Oops, I just noticed that you said your tables were in different databases. I repeated my test that way but for me it still worked as expected.

Thanks John.

But my data is more like:

screenshot.jpeg

The value is null for both the source and the target. It is not able to match these.

Any luck with such scenario?

Nulls should be no problem. Look back to my screen shot of my first reply, and you see that the COMM column is null for almost all rows. Those rows in your example are identical, but in your actual data - do the rows have differences in other columns? Toad has an option to highlight null values in yellow in the grids and I am wondering if they are just highlighted because of that. Another possible explanation is that they differ by whitespace.

If that’s not it, please post a script so I can reproduce the problem.

Try this:

create table emp
(EMPNO number,
EMPNAME varchar2(50),
JOB varchar2(50),
DEPTNO varchar2(50)
);

insert into emp values (1,'Sam','','10');
insert into emp values (2,'Smith','Manager','10');
insert into emp values (3,'Dan','Clerk','');
insert into emp values (4,'Kyle','Clerk','20');

commit;

create table emp_1 as select * from emp;

Now run Compare data on these 2 tables.

It gives the following result:

So when the data volume is huge, it becomes another tasks to compare all the data just to find out that it wasn't able to compare null records.

I'm trying to figure out in case there is some setting in Toad which would help in comparing the records with null columns as well.

Please let me know if it provides clarity on the issue.

When you get to the step “specify columns to uniquely identify each row”, which columns are you including? If the table has a primary key, the primary key columns should show up automatically and you should not change them.

If the table does NOT have a primary key, but there is a column (or two) that could be used as such, you may specify that. Do not specify a column with nulls as a key column. I think maybe that is what’s happening. When you manually select key columns, Toad does a check to make sure that rows are uniquely identified, but Toad does not check to make sure that the columns to do not contains nulls. I’ll fix that.

If you don’t have any key columns that do not contain nulls, then leave that step blank. It is better to leave it blank than to specify all columns.

Finally, if the comparison is too slow, you can speed things up by changing the “Matching rows” option to “Ignore”.

Also, you might want to try the 12.12 beta. I have made some improvements queries that data compare uses in 12.12 (especially if no key columns are used).

Thanks John. It would be helpful if this can be fixed.

Also I tried the option of not selecting any column for comparison, as mentioned by you.

It works fine if the objects are in the same database but for a different database, it specifically asks for picking the key columns for comparison.

Thanks John. It would be helpful if this can be fixed.

I don't really see anything that is broken here except that Toad allowed you to choose a column with nulls to be used as a key. The definition of a primary key is "all unique values and no nulls". We need this to uniquely identify a row.

Is there no non-null key column(s) for the table that you can use?

If there is not, what about a creating database link so that you can use one connection to access both tables? If you can use a database link, I would suggest downloading the 12.12 beta for the comparison. 12.11 had some problems that made the comparison very slow when going through a DB Link with no key specified.

Finally, If you just want to sync the tables and you don't care about looking at the differences first, use Database -> Compare -> Multiple Tables instead. That window is designed to sync data in all tables in a pair of schemas, but you can limit it to just specific tables if you want. You will still need either a primary key or a DB Link to compare tables in different databases, but that window just skips straight to synchronization without displaying the rows that differ, so it is faster. And it can automatically create the DB link for you and then drop it after the sync is complete. I just tested this in 12.11 on a table with no key and about 100,000 rows and it synced in about 15 seconds across a DB Link.

I get your point and the workarounds that you mentioned. But there are certain restrictions because of which the other workarounds couldn’t be used and this was the only way.

I’ll check in version 12.12 if this can work out. My problem was null comparison handling; the way we can do using queries. If that is something which toad isn’t designed to handle, I’ll look for some other way of comparing data.

Thanks for your inputs.

Not being able to use a DB Link is really making this difficult for you. For a pair of large tables with no primary key, I am beginning to think that your best course of action might just be to use datapump to export the source table, then import it into the target.

Transportable tablespaces would be another good way to get a large table moved from one DB to another.

I have been looking at this more closely this morning, and it seems that the problem is not comparison but synchronization.

If you specify a key, the sync statements that we run internally use the key for the where clause, and it assumes that the key is never null. We make statements like

update EMP
set (non_key_column1 = :value1, non_key_column = :value2, etc…)
where key_column1 = :value3
and key_column2 = :value4

The above doesn’t work when the key columns have null values. In that case, IS NULL should be used instead.

If you click the 2nd sync button, to send a sync script to the editor, then we don’t use variables, and statements have literals with IS NULL where appropriate.

So I think if you just run the sync script in the editor, (or save to a file and run in SQL*Plus) then you’ll be able to sync your tables.

Here the process that is being followed is that the database migration activity is done by the DBA. We send the insert scripts which he runs and then we compare the two environments for any differences.

For this, we are using the toad data compare. And for the data where I’m getting mismatch, I’m copying it to a notepad and then comparing it for now.

Had there been an option like a checkbox for handling null data like we do in a query, it would have made the data compare a single step process.

I have made the following changes for Toad 12.13

Comparison of tables in same connection:

  • Nulls in keys are not supported, so before the comparison, Toad will verify that all key values are non-null. It will not allow you to proceed if nulls exist in your key columns.

  • A workaround would be to just not specify a key for the comparison.
    Comparison of tables in different connections:

  • A key is required for these comparions

  • Nulls in keys will now be supported.

  • If you have no key without nulls, then a workaround would be to specify a DB link between the two databases, and then compare without keys.