Toad World® Forums

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

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.

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.

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.

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?

This helps "a little". :slight_smile: 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.

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).

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.

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?

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

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

Hi Dirk,

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

-John