Does anyone know of any Oracle dictionary views that hold references of 'real' columns to virtual columns?
For example lets say I have a table like this:
create table t (
c1 varchar2(5)
,c2 as (c1 || '*')
)
I'm after a way of determining that c1 has a dependency of c2 (without trying to parse user_tab_cols.data_default). I'm writing a schema synchronising tool and I'm trying to work out what I need to do to synchronise a table with a 'model' performing the minimal amount of work. If a model showed that c1 from the above table changed from varchar2(5) to varchar2(6), I'm hoping to be able to determine that I need to do something like:
alter table t modify( c2 as ( null ) );
alter table t modify( c1 varchar2(6) );
alter table t modify( c2 as ( c1 || '*' ) );
And avoid an ORA-54031.
[EDIT]
It seems I wasn't clear enough in explaining my problem so here goes.
When our developers start working on a bug they create a git branch of the application along with a database schema that is populated with the contents of the master git branch. While working on the bug the developer may make DML changes or may occasionally make DDL changes (as well as code changes of course). Once their bug has been completed and reviewed, their git branch is merged with master. The sync tool allows a complete 'diff' to be done on the database schema – both from a content and a structure perspective. This forms part of the review. The developer uses the sync tool to generate an export of their schema and this export is part of the branch. After the bug branch is merged to master, the master database schema is sync'd from the export done by the developer.
Yes, scripts would be one solution to the problem but it requires a level of discipline that is not present in all developers. The sync tool currently handles the scenario I outlined but imo somewhat clumsily. The reason I asked my original question was that if I was able to determine via a dictionary view the relationship between real and virtual columns, the sync sql my tool generates would be more 'surgical'.
Thanks.
Best Answer
User_tab_cols shows that there is a dependancy when virtual_column contains 'YES', which answers your need to determine if there is any dependency at all.
In order to determine what the actual virtual column consists of, one still needs to parse data_default.
SELECT column_name , virtual_column , segment_column_id , internal_column_id FROM user_tab_cols WHERE table_name = 'T' ORDER BY column_id;