I'm doing a Compare Schema and expecting NO difference, but instead, Compare Schema returned a difference in a view and lists the difference as 'column.' A script compare shows not difference in columns at all. Even manually scanning the view shows no difference in column or column type.
The view might references two tables and they both have one column with the same name. If you run the view's select-statement, see if you actually get the error 'ORA-00918: column ambiguously defined.'
For example in pseudo code:
Create View
select column a,
column b,
column c
from table T1 and table T2
Create table T1
column a
column b
Create table T2
column a
column c
T1 and T2 bot have column 'a.' So in the view script which table does column 'a' belong to?
WORKAROUND:
1. Run your view's SELECT script to see if error 'ORA-00918: column ambiguously defined' comes up
2. If so, check to see which columns in the view script are being reference by multiple tables
3. Make the necessary corrections to the view script or the tables themselves
STATUS:
Enhancement request ST62501 has been submitted to Development for consideration in a future release of Toad for Oracle. The enhancement will make the difference description more specific to address issue so this kind.
