[15.0.94] Comparing a schema on a 10g server and an 18c server lists identical views as different

[dirk.mika1]

The views are shown as different because on the 18c server the BEQUEATH clause is output with "BEQUEATH definer", which does not exist on the 10g server.
Since "BEQUEATH definer" is the default and therefore the views behave identically I would consider them identical.

[JohnDorlon]

OK, So I just tried this in Compare Schemas and saw that we are already handling this.

If that's the only difference in the view, it won't cause the view to be flagged as different. But if there are other differences, you will see the "bequeath definer" appear in the source for one but not the other.

[dirk.mika1]

This is strange. I have made the comparison again.
Out of 84 views in the scheme, 79 are listed as different. (at "objects which differ / Views").
When I click on a view there, the only difference found in the preview is "BEQUEATCH DEFINER".
However, the same 79 views are listed at "objects which differ / View Colunms". All with an entry with "Dataype" in the column "Differs By". The underlying tables, however, are not listed as different.
The option "Compare BYTE/CHAR in Columns" is active.
I still checked for a table that serves as the basis for one of the views that are supposedly different to see if the listed column is actually different and found no difference.

[JohnDorlon]

There must be something different values in dba_tab_columns between the views. Most likely type, precision, or scale. We only compare char_used if both have a value.

Does it help if you uncheck here?

[dirk.mika1]

This helps "a little". Now, interestingly, only one view is listed. But still with a different column type.

I took a look at the entries for the view in all_tab_columns and there is indeed 'UNDEFINED' in the column 'DATA_TYPE'.

This is probably due to the fact that the view uses a table in a different schema that does not exist on one server.

[dirk.mika1]

Hmm, maybe it's a characterset issue. On one server (AL32UTF8), a view column is reported with a DATA_LENGTH of 32, while it's 8 on the other server (WE8MSWIN1252).

[JohnDorlon]

We don't compare Data_length. Only data_type, precision, scale, and char_used.

Edit: See my next reply. We compare data_length for all data types except timestamp.

[dirk.mika1]

Ok, this is on Server A (18c)

COLUMN_NAME	DATA_TYPE	DATA_PRECISION	DATA_SCALE	CHAR_USED
ID_BANK	VARCHAR2			C
NAME	VARCHAR2			C
DESCRIPTION	VARCHAR2			C
ALPHA3_C	VARCHAR2			C
ACCOUNT_NO_ALGO	VARCHAR2			C
RECORD_CREATED	DATE
RECORD_MODIFIED	DATE

and this is on Server B (10g):

COLUMN_NAME	DATA_TYPE	DATA_PRECISION	DATA_SCALE	CHAR_USED
ID_BANK	VARCHAR2			C
NAME	VARCHAR2			C
DESCRIPTION	VARCHAR2			C
ALPHA3_C	VARCHAR2			C
ACCOUNT_NO_ALGO	VARCHAR2			C
RECORD_CREATED	DATE
RECORD_MODIFIED	DATE

Using

SELECT column_name, data_type, data_precision, data_scale, char_used
  FROM all_tab_columns
 WHERE table_name = 'BANKS';

But I still get this:

How do you compare the length of VARCHAR2 columns?

[JohnDorlon]

Oops, sorry. I misspoke earlier. We compare data_length for all datatypes except timestamp.

[dirk.mika1]

So, that's causing trouble in this case, since it's 32 on the 18c, while being 8 on 10g. Maybe comparing char_length is better in this case.

18c Server:

COLUMN_NAME	DATA_TYPE	DATA_LENGTH	CHAR_LENGTH	DATA_PRECISION	DATA_SCALE	CHAR_USED
ID_BANK	VARCHAR2	32	8			C
NAME	VARCHAR2	120	30			C
DESCRIPTION	VARCHAR2	800	200			C
ALPHA3_C	VARCHAR2	12	3			C
ACCOUNT_NO_ALGO	VARCHAR2	40	10			C
RECORD_CREATED	DATE	7	0
RECORD_MODIFIED	DATE	7	0

10g Server:

COLUMN_NAME	DATA_TYPE	DATA_LENGTH	CHAR_LENGTH	DATA_PRECISION	DATA_SCALE	CHAR_USED
ID_BANK	VARCHAR2	8	8			C
NAME	VARCHAR2	30	30			C
DESCRIPTION	VARCHAR2	200	200			C
ALPHA3_C	VARCHAR2	3	3			C
ACCOUNT_NO_ALGO	VARCHAR2	10	10			C
RECORD_CREATED	DATE	7	0
RECORD_MODIFIED	DATE	7	0

BR
Dirk

[JohnDorlon]

Hi Dirk,

I think you might be right. I'll take a look at this tomorrow.

-John