Bernhard S
Member³
I have a virtual column in a table which is based on a deterministic packaged function.
In another schema I have the identical table based with the identical virtual column based on the identical deterministic packaged function in this other schema.
However when I compare both tables using "PL/SQL-Developer" - "Compare User Objects" it shows me a wrong difference.
A look in "Show Differences" shows, that the package on which the virtual column is based is schema qualified there and therefore both are supposedly different.
The same happens with the index on those virtual columns as well.
PSD should now take the local schema into account when comparing schema objects though! Those of course are always different in all differently named schemas!
The resulting adjustment SQL anyway won't work either.
It tries an "ALTER TABLE ... MODIFY ... " first which fails with
ORA-54022 "Virtual column expression cannot be changed because an index is defined on column"
since the drop of the index is done only afterwards. Even I first drop the index it fails anyway with
ORA-54023 "Virtual column expression cannot be changed because a constraint is defined on column"
as the virtual column is defined as NOT NULLable. Only when this one is removed the "ALTER TABLE ... MODIFY ... " would work.
In another schema I have the identical table based with the identical virtual column based on the identical deterministic packaged function in this other schema.
However when I compare both tables using "PL/SQL-Developer" - "Compare User Objects" it shows me a wrong difference.
A look in "Show Differences" shows, that the package on which the virtual column is based is schema qualified there and therefore both are supposedly different.
The same happens with the index on those virtual columns as well.
PSD should now take the local schema into account when comparing schema objects though! Those of course are always different in all differently named schemas!
The resulting adjustment SQL anyway won't work either.
It tries an "ALTER TABLE ... MODIFY ... " first which fails with
ORA-54022 "Virtual column expression cannot be changed because an index is defined on column"
since the drop of the index is done only afterwards. Even I first drop the index it fails anyway with
ORA-54023 "Virtual column expression cannot be changed because a constraint is defined on column"
as the virtual column is defined as NOT NULLable. Only when this one is removed the "ALTER TABLE ... MODIFY ... " would work.
