for some reason the latest beta is saying i have 1153 datatype differences in my views, but the tables are the same. Also the 15.0 toad works fine.
Hi Marc,
Toad is looking at the datatypes in DBA/ALL/USER_TAB_COLUMNS for the views, not the underlying objects. You will probably see some difference there.
Does it behave if you uncheck this option?
Same results with the checkbox on or off.
I just tested this and was not able to reproduce it.
Please let me know the results of this SQL for one of the views in both schemas:
Select C.COLUMN_NAME, C.DATA_LENGTH, C.DATA_PRECISION, C.DATA_SCALE, C.NULLABLE, C.TABLE_NAME, C.DATA_TYPE, C.DATA_TYPE_MOD, C.DATA_TYPE_OWNER
, C.CHAR_USED, C.CHAR_LENGTH
, C.HIDDEN_COLUMN
FROM SYS.DBA_TAB_COLS C
WHERE C.OWNER = your schema
AND C.TABLE_NAME = your view
AND C.USER_GENERATED = 'YES'
order by c.table_name, c.column_id, c.internal_column_id;
I don't know if it's the same cause, but for me triggers are detected as different even though they are the same.
To be exact, they differ in the DESCRIPTION. In one scheme it looks like this:
"SP_DEV_1_18".TR_A2P_IS_DEFAULT
BEFORE UPDATE OF is_default
ON account2person
FOR EACH ROW
in the other like this:
tr_a2p_is_default
BEFORE UPDATE OF is_default
ON account2person
FOR EACH ROW
However, I have enabled the "Parse for schema names" option on the tiggers and disabled the "Include schema name in Migration DDL" option on Script.
John, you query did not work, there is no USER_GENERATED column. Maybe that is the issue, I am on oracle 11g? I removed the column and change the query to use a minus, all the data is same.
SELECT c.column_name,
c.data_length,
c.data_precision,
c.data_scale,
c.nullable,
c.table_name,
c.data_type,
c.data_type_mod,
c.data_type_owner,
c.char_used,
c.char_length,
c.hidden_column
FROM sys.dba_tab_cols c
WHERE c.owner = 'FX_VAL_A' AND c.table_name = 'ASSET_CLASS' /AND c.user_generated = 'YES'/
MINUS
SELECT c.column_name,
c.data_length,
c.data_precision,
c.data_scale,
c.nullable,
c.table_name,
c.data_type,
c.data_type_mod,
c.data_type_owner,
c.char_used,
c.char_length,
c.hidden_column
FROM sys.dba_tab_cols c
WHERE c.owner = 'FX_VAL_B' AND c.table_name = 'ASSET_CLASS' /AND c.user_generated = 'YES'/
ORDER BY table_name, column_name
Hi @marc_109, sorry, I gave you the query that we use for 19c.
For 11g, we just leave that column out and select from ALL_TAB_COLUMNS instead of ALL_TAB_COLS.
I just tested on 11g. For me, the only differences that I am seeing are legit (usually due to one of the views being INVALID and having UNDEFINED as a data type. I don't know why your query returns nothing (unless maybe it was undefined at the time of comparison and then later when you ran the query, the view became valid)
I do see that the "Prec/Scale" option is not preventing this difference from being shown. But that's expected because datatype is not the same as precision/scale.
This comes up from time to time though. I can add an option in there to not compare columns at all. If you are comparing tables and view SQL, it doesn't seem really necessary to also look at the view columns.
@dirk.mika1 Is it the case differences (tr_a2p_is_default vs TR_A2P_IS_DEFAULT ) causing the difference?
There is an option for case sensitivity here:
That option does not make any difference. To be on the safe side I tested both variants again.
However, I noticed that I had disabled the "Exclude whitespace" option. :-/ That was the cause.
1 Like
I just rebuilt my schemas and latest beta, now it looks better, not sure why. but thank you
I added an option in compare schemas (under the place where you check to include Views) where you can include/exclude data type comparisons. It doesn't seem necessary if you are comparing tables also.