[SOLVED] How to check from where the column value is populating

How to check from where the column value is populating

I have table TEST_ORD where i have column ORD_DAYID which is virtual column. I want to know from where and how the value in this coumn is populating. Because i cant use this column in insert statement as it is virtual column.

I tried the below query but its not giving me the information from where and how the value in the column is populating:

Select * from all_source where upper(TEST) like '%TEST_ORD_ID%';

Solution

The expression used to generate the virtual column is listed in the DATA_DEFAULT column of the [DBA|ALL|USER]_TAB_COLUMNS views.

For eg:

Created table with virtual column which wil be populated as per the definiton.

CREATE drop TABLE virtab(
  id          NUMBER,
  first_name  VARCHAR2(10),
  last_name   VARCHAR2(10),
  salary      NUMBER(9,2),
  comm1       NUMBER(3),
  comm2       NUMBER(3),
  salary1     AS (ROUND(salary*(1+comm1/100),2)),
  salary2     NUMBER GENERATED ALWAYS AS (ROUND(salary*(1+comm2/100),2)) VIRTUAL,
  CONSTRAINT employees_pk PRIMARY KEY (id)
);

Selection

select *  from DBA_TAB_COLUMNS where table_name = 'VIRTAB' and column_name = 'SALARY2'

Output:

SQL> select DATA_DEFAULT from DBA_TAB_COLUMNS where table_name = 'VIRTAB' and column_name = 'SALARY2';

DATA_DEFAULT
--------------------------------------------------------------------------------
ROUND("SALARY"*(1+"COMM2"/100),2)