"Compare Table Data" generates empty script in V11 and V12

[Frank Schmitt]

This one's been bugging me for a while - sometimes (not always - it seems to depend on the table), "Compare Table Data" generates an empty script although it mentions that it found differences.

This bug was introduced in V11 and still persists in V12 (I haven't checked the V13 beta). Here's the output from a comparison using 12.0.5.1828:

rem Differences between user@DEV and user@PROD, created on 26.11.2018
rem Press Apply button, or run in Command Window or SQL*Plus connected as user@PROD

-- TABLE PROFILE
-- Deleting ...
-- Updating ...
-- Inserting ...
-- 70 row(s) total
-- 208 row(s) read
-- 1 row(s) deleted
-- 47 row(s) inserted
-- 10 row(s) updated

And here's the same comparison in 10.0.5.1710:

rem Differences between user@DEV and user@PROD, created on 26.11.2018
rem Press Apply button, or run in Command Window or SQL*Plus connected as user@PROD

-- TABLE PROFILE
-- Deleting ...
-- Updating ...
-- Inserting ...
-- 70 row(s) total
-- 208 row(s) read
-- 1 row(s) deleted
-- 47 row(s) inserted
-- 10 row(s) updated

DELETE FROM PROFILE T WHERE PROFILE_PK = 485;



Please fix this - it's a real pain because I have to switch back to V10 every time I want to reliably compare table data.

Kind regards,
Frank

 

[Marco Kalter]

Is this reproducible for a specific table? If so, can you let me know the table creation DDL?

 

[Frank Schmitt]

It's reproducible; here's the DDL statement for the table on the source instance (I've omitted the schema name and storage clauses):

CREATE TABLE PROFILE
( PROFILE_PK NUMBER,
PROFILE_NAME VARCHAR2(255) NOT NULL ENABLE,
PROFILE_DESCRIPTION VARCHAR2(4000),
CREATED_BY VARCHAR2(30) DEFAULT user,
CREATED_ON DATE DEFAULT sysdate,
UPDATED_BY VARCHAR2(30) DEFAULT null,
UPDATED_ON DATE DEFAULT null,
PROFILE_CATEGORY_FK NUMBER DEFAULT 1,
FIRST_HEADER_ROW NUMBER DEFAULT 1 NOT NULL ENABLE,
LAST_HEADER_ROW NUMBER DEFAULT 1 NOT NULL ENABLE,
FIRST_DATA_ROW NUMBER DEFAULT 2 NOT NULL ENABLE,
ACTIVE NUMBER DEFAULT 1 NOT NULL ENABLE,
CONSTRAINT PROFILE_PK PRIMARY KEY (PROFILE_PK) ENABLE,
CONSTRAINT PROFILE_CATEGORY_FK FOREIGN KEY (PROFILE_CATEGORY_FK)
REFERENCES PROFILE_CATEGORY (PROFILE_CATEGORY_PK) ENABLE
);

CREATE UNIQUE INDEX PROFILE_CAT_UPLOAD_SUIT_UC ON PROFILE (
CASE PROFILE_CATEGORY_FK
WHEN 2 THEN PROFILE_CATEGORY_FK
WHEN 3 THEN PROFILE_CATEGORY_FK
WHEN 5 THEN PROFILE_CATEGORY_FK
ELSE NULL END );

ALTER TABLE PROFILE MODIFY (PROFILE_NAME NOT NULL ENABLE);
ALTER TABLE PROFILE MODIFY (FIRST_HEADER_ROW NOT NULL ENABLE);
ALTER TABLE PROFILE MODIFY (LAST_HEADER_ROW NOT NULL ENABLE);
ALTER TABLE PROFILE MODIFY (FIRST_DATA_ROW NOT NULL ENABLE);
ALTER TABLE PROFILE MODIFY (ACTIVE NOT NULL ENABLE);
ALTER TABLE PROFILE ADD CONSTRAINT PROFILE_PK PRIMARY KEY (PROFILE_PK) ENABLE;

And here for the target instance:

CREATE TABLE PROFILE
( PROFILE_PK NUMBER,
PROFILE_NAME VARCHAR2(255) NOT NULL ENABLE,
PROFILE_DESCRIPTION VARCHAR2(4000),
CREATED_BY VARCHAR2(30) DEFAULT user,
CREATED_ON DATE DEFAULT sysdate,
UPDATED_BY VARCHAR2(30) DEFAULT null,
UPDATED_ON DATE DEFAULT null,
PROFILE_CATEGORY_FK NUMBER DEFAULT 1,
FIRST_HEADER_ROW NUMBER DEFAULT 1 NOT NULL ENABLE,
LAST_HEADER_ROW NUMBER DEFAULT 1 NOT NULL ENABLE,
FIRST_DATA_ROW NUMBER DEFAULT 2 NOT NULL ENABLE,
ACTIVE NUMBER DEFAULT 1 NOT NULL ENABLE,
CONSTRAINT PROFILE_PK PRIMARY KEY (PROFILE_PK) ENABLE,
CONSTRAINT PROFILE_CATEGORY_FK FOREIGN KEY (PROFILE_CATEGORY_FK)
REFERENCES PROFILE_CATEGORY (PROFILE_CATEGORY_PK) ENABLE
);

CREATE UNIQUE INDEX PROFILE_CAT_UPLOAD_SUIT_UC ON PROFILE (
CASE PROFILE_CATEGORY_FK
WHEN 2 THEN PROFILE_CATEGORY_FK
WHEN 3 THEN PROFILE_CATEGORY_FK
ELSE NULL END );

CREATE UNIQUE INDEX PROFILE_PK ON PROFILE (PROFILE_PK);
ALTER TABLE PROFILE ADD CONSTRAINT PROFILE_PK PRIMARY KEY (PROFILE_PK) ENABLE;
ALTER TABLE PROFILE MODIFY (PROFILE_NAME NOT NULL ENABLE);
ALTER TABLE PROFILE MODIFY (ACTIVE NOT NULL ENABLE);
ALTER TABLE PROFILE MODIFY (FIRST_DATA_ROW NOT NULL ENABLE);
ALTER TABLE PROFILE MODIFY (LAST_HEADER_ROW NOT NULL ENABLE);
ALTER TABLE PROFILE MODIFY (FIRST_HEADER_ROW NOT NULL ENABLE);

Thanks for looking into this,
Frank

 

[Marco Kalter]

Thanks. We'll try to reproduce this.

 

[YikeSS]

We have the same issue in V11, V12 and V13.

Thanks.

Regards:
Zsolt Kertesz

 

[F. Barba]

Hello

We just have our new PL/SQL Developer installed yesterday and observed rapidly the same problem :-(

We can reproduce it with a simple table like the following created into two different schemas :

create table TABLE_TEST
(
rec_id INTEGER not null,
rec_string VARCHAR2(32)
);

alter table TABLE_TEST add constraint PK_TABLE_TEST primary key (REC_ID);

As soon as we insert a string with one extended character into rec_string, the "Compare Table Data" tool don't show the SQL script anymore !

Thanks in advance for your help

Francis

 

[Marco Kalter]

It works fine when I try this scenario. Can you send a screenshot to support@allroundautomations.com?

 

[YikeSS]

We have the same issue in 13.006.1911:

examples results:

#1
"rem Differences between xxx@xxx and xxx@yyy, created on 2019.09.13.
rem Press Apply button, or run in Command Window or SQL*Plus connected as yyy@yyy

-- TABLE DIC_IG_RENDSZER
-- Deleting ...
-- Updating ...
-- Inserting ...
-- 2 row(s) total
-- 2 row(s) read
-- 0 row(s) deleted
-- 2 row(s) inserted
-- 0 row(s) updated"

#2
"rem Differences between xxx@xxx and xxx@yyy, created on 2019.09.13.
rem Press Apply button, or run in Command Window or SQL*Plus connected as yyy@yyy

-- TABLE ATE_D_CODE
-- Deleting ...
-- Updating ...
-- Inserting ...
-- 31 row(s) total
-- 79 row(s) read
-- 7 row(s) deleted
-- 24 row(s) inserted
-- 0 row(s) updated"

That's all. No DELETEs or INSERTs.

Regards:
Zsolt Kertesz

 

[Marco Kalter]

We have a fix for this problem. If you are interested, send an e-mail to support@allroundautomations.com.