Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I would like to find computed column list in oracle database using Oracle Data Dictionary Views.

I would like to add more information. Suppose I have following computed/calculated column in database.

ALTER TABLE HR.EMPLOYEES
 ADD FULL_NAME AS ( FIRST_NAME || ' ' || LAST_NAME);

I can select this column value in Select Statements.

SELECT EMPLOYEE_ID,FULL_NAME FROM HR.EMPLOYEES;

Results

... 174 Ellen Abel ...

I can see this column in TAB_COLUMNS view.

 select * from all_tab_columns   C 
 WHERE
 1 = 1
 AND  C.table_name = 'EMPLOYEES'
 AND C.OWNER = 'HR'
 AND COLUMN_NAME = 'FULL_NAME'
 ;

I would like to find given schema, table and column find that if this column is calculated/computed?

Wrongly I thought that INDEXES view gives me this information. But following select returns no rows. This gives only functional indexes.

SELECT index_name,index_Type,I.*
FROM ALL_indexes I
WHERE 
1 = 1
AND INDEX_TYPE LIKE 'FUNCTION-BASED%'
AND I.OWNER = 'HR'
share|improve this question
add comment

1 Answer

up vote 2 down vote accepted

The column USER_TAB_COLS.virtual_column = 'YES' indicates if it's a virtual column.

You can get the expressions for each column with:

SELECT * 
FROM user_tab_columns
WHERE data_default IS NOT NULL;
share|improve this answer
    
Thank you. It seems that there is difference between ALL_TAB_COLS and ALL_TAB_COLUMNS as stated here (kr.forums.oracle.com/forums/thread.jspa?threadID=1123911). ALL_TAB_COLS view differs from "ALL_TAB_COLUMNS" in that hidden columns are not filtered out." –  Atilla Ozgur Jul 11 '12 at 13:23
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.