Toad World® Forums

How to disable rowid in Data compare sync script

When i compare the data of 2 tables between 2 different oracle database servers and one of column of the row has been updated, the sync scipt generates an update statement, however, the where clause has a rowid, Is there a way to to use the Primary key value rather than the rowid.

e.g

This is the update statement that is generated, which is perfectly valid,

UPDATE MY_SCHEMA.TAB:MY_TABLE SET TESTLIST_NM = ‘This is a test’ WHERE ROWID = ‘AAAYgeAALAAAAHlAAI’;

However i would like the where clause to have the name of the Primary Key and the value, as below (TESTLIST_CD is the Primary Key column)

UPDATE MY_SCHEMA.TAB:MY_TABLE SET TESTLIST_NM = ‘This is a test’ WHERE TESTLIST_CD = ‘CODE_O1’;

The Compare Data screen, for me, does use the primary key in the script. Pay attention to the steps as you are going through the “compare data” window. One of them asks you to identify the primary key columns. It should detect them automatically but if it does not, you can choose them yourself. If you do that, the primary key columns will be used in the sync script. If not, we’ll use ROWID.

May I join this thread. Using Ver 12.6.0.53, I have same problem. I do a compare. The PK’s are identified automatically and listed to be used. The script created still uses ROWID ?? I need to delete some specific rows from the script but can’t do so without knowing what PK’s they refer? Hmmm

I just tried this in 12.6 and the sync script for me contained primary key fields, not ROWID.

On the 2nd step (options), choose “Row by Row”.

On the 3rd step (specify columns to identify each row), chose primary key columns if they are not automatically selected for you.

If columns aren’t specified on the 3rd step, the sync script deletes non-matching rows (using ROWID) from the target table, then re-inserts them from the source. If columns ARE specified, then they are updated using the PK columns.

If you are seeing something else, send me some screen shots of your steps. You can post it here or email to me directly: john.dorlon@quest.com

What I said in my first reply is true if both tables are in the same database. I just tried it with different databases and I am getting ROWIDs in the sync script.

OK, this is fixed for the next beta (12.9.0.55).