Compare user objects - EVIDENT BUG

[paweld]

Hello! At the very beginning must stress my surprise that the bug which follows haven't been fixed so far. It's above one year when it was reported (see previous report ).

Here is support info from my PC:

PL/SQL Developer
  Version 7.0.1.1066 (MBCS)
  97.33630 - 20 user license
  Windows XP version 6,0 (build 2600) Dodatek Service Pack 2

Using
  Home: OraHome92
  DLL: C:\oracle\ora92\bin\oci.dll
  OCI: version 9.2
  Oracle9i Enterprise Edition Release 9.2.0.7.0
  Character size: 1 byte(s)

And below simple testcase which (in my opinion) proves that 'Compare user objects' function doesn't work as it should:

connect sys/change_on_install@targetdb as sysdba
drop user zijtesi cascade
/
create user zijtesi identified by zijtesi
/
grant connect, dba to zijtesi
/
create table zijtesi.first_test_drop_col (
  col_1 number(1),
  col_2 number(1),
  col_3 number(1)
  )
/
create table zijtesi.second_test_drop_col (
  col_1 number(1),
  col_2 number(1),
  col_3 number(1)
  )
/
create table zijtesi.third_test_drop_col (
  col_1 number(1),
  col_2 number(1),
  col_3 number(1)
  )
/
connect sys/change_on_install@sourcedb as sysdba
drop user zijtesi cascade
/
create user zijtesi identified by zijtesi
/
grant connect, dba to zijtesi
/
create table zijtesi.first_test_drop_col (
  col_1 number(1),
  col_2 number(1)
  )
/
create table zijtesi.second_test_drop_col (
  col_1 number(1),
  col_3 number(1)
  )
/
create table zijtesi.third_test_drop_col (
  col_2 number(1),
  col_3 number(1)
  )
/

After creating tables connect as user zijtesi to sourcedb and run 'Compare user objects' function against user zijtesi at targetdb. Below is the result which I've received:

rem Differences between ZIJTESI@EBOKORA and ZIJTESI@BOKORA, created on 2006-04-20
rem Press Apply button, or run in Command Window or SQL*Plus connected as ZIJTESI@BOKORA

-----------------------------------------
--  Changed table first_test_drop_col  --
-----------------------------------------
-- Drop columns
alter table FIRST_TEST_DROP_COL drop column COL_3;

Hope that at last someone will fix it.

 

[Marco Kalter]

Hmm, we'll check it out and will fix it asap.

 

[paweld]

Hello Marco! I've thought about the possibility of stating while comparing tables that a column had been deleted. It is rather difficult problem, I suppose. Is it solvable? It may turn out that only in simple cases (as the FIRST_TEST_DROP_COL example). However the script generated by PL/SQL developer is misleading at the moment since it suggests that only FIRST_TEST_DROP_COL had changed. Although generating proper and correct script may be problematic, however stating that there are differences seems to be quite easy. So in too difficult cases there should be at least info that number of columns changed or something like that and involved objects should figure on difference list.