Compare Objects order

[Eggie]

I found that the order of the columns is important when comparing two database objects.

DB1:
create table PIL_UNITS_OF_MEASURE
(
ID NUMBER(12) not null,
CODE VARCHAR2(10) not null,
ACTIVE VARCHAR2(1) not null,
DESCRIPTION VARCHAR2(1000),
SCALE NUMBER not null,
SI_CONVERSION NUMBER,
SI_ZERO_ORDER NUMBER,
SYSTEM VARCHAR2(1),
TYPE VARCHAR2(1) not null,
VERSION NUMBER(38) default 1 not null,
DATE_CREATED DATE not null,
CREATED_BY VARCHAR2(30),
DATE_MODIFIED DATE,
MODIFIED_BY VARCHAR2(30)
)

And DB2:
create table PIL_UNITS_OF_MEASURE
(
ID NUMBER(12) not null,
CODE VARCHAR2(10) not null,
ACTIVE VARCHAR2(1) not null,
TYPE VARCHAR2(1) not null,
DESCRIPTION VARCHAR2(1000),
SCALE NUMBER not null,
SI_CONVERSION NUMBER,
SI_ZERO_ORDER NUMBER,
SYSTEM VARCHAR2(1),
VERSION NUMBER(38) default 1 not null,
DATE_CREATED DATE not null,
CREATED_BY VARCHAR2(30),
DATE_MODIFIED DATE,
MODIFIED_BY VARCHAR2(30)
)

Since the column named "Type" is in a different position, the generated "alter" scripts becomes:

-- Add/modify columns
alter table PIL_UNITS_OF_MEASURE rename column SI_CONVERSION to SCALE;
alter table PIL_UNITS_OF_MEASURE modify SCALE not null;
alter table PIL_UNITS_OF_MEASURE rename column SI_ZERO_ORDER to SI_CONVERSION;
alter table PIL_UNITS_OF_MEASURE rename column TYPE to SYSTEM;
alter table PIL_UNITS_OF_MEASURE modify SYSTEM null;

Which will fail at the first line because the column "Scale" is already there . Maybe the columns should be ordered before comparing tables?

Regards Egbert

 

[Marco Kalter]

This was not the intention. The column order should not matter for table comparisons. We'll fix it.