0

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%';
Andrew
  • 3,632
  • 24
  • 64
  • 113

1 Answers1

1

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)
XING
  • 9,608
  • 4
  • 22
  • 38
  • no this is not what i want. But how they ar inserting the value in this column ? From which procedure,package ? – Andrew Aug 03 '17 at 09:53
  • But then you never mentioned this in your question. You question say `I want to know from where and how the value in this coumn is populating` . Where did you mention that its getting populated from a procedure and also i wanted to tell you the purpose of `virtual` column is to have the value to the column is calulated `automatically` when the conditions are met. So neither `procedure` nor any `insert` statement can populate this column. it would be automatically calculated as per defintion. See my example – XING Aug 03 '17 at 09:56