Home » SQL & PL/SQL » SQL & PL/SQL » Getting the list of columns of a VIEW from data dictionary in pl sql (10g, 10.2.0.1.0, Windows)
Getting the list of columns of a VIEW from data dictionary in pl sql [message #413082] Tue, 14 July 2009 05:43 Go to next message
be2sp1
Messages: 52
Registered: September 2005
Location: India
Member
hi,

As a part of project requirement i want the list of all columns of a view in PL/SQL. This view will be created by a user and i am supposed to find out the different columns in the view and then update properties for them (metadata) in another table. I see that the source code of the VIEW is present in the TEXT column of the USER_VIEWS data dictionary, however, i am not able to figure out how to get the list of columns.

kindly suggest. A sample view is attached for reference.

Thanks
  • Attachment: view.sql
    (Size: 0.31KB, Downloaded 107 times)
Re: Getting the list of columns of a VIEW from data dictionary in pl sql [message #413099 is a reply to message #413082] Tue, 14 July 2009 06:40 Go to previous messageGo to next message
ashoka_bl
Messages: 398
Registered: November 2006
Location: Bangalore
Senior Member

I guess, this is what you are looking for.

SQL> create or replace view emp_view as select empno,ename,sal from emp;

View created.

SQL> select column_name from user_tab_columns where table_name='EMP_VIEW';

COLUMN_NAME
------------------------------
EMPNO
ENAME
SAL
Re: Getting the list of columns of a VIEW from data dictionary in pl sql [message #413165 is a reply to message #413099] Tue, 14 July 2009 11:59 Go to previous messageGo to next message
be2sp1
Messages: 52
Registered: September 2005
Location: India
Member
thanks a lot.

I presumed that Views are not stored in USER_TAB_COLUMNS and was searching for a solution using PL/SQL. Will try to put more effort in figuring out such things going forward.
Re: Getting the list of columns of a VIEW from data dictionary in pl sql [message #413167 is a reply to message #413165] Tue, 14 July 2009 12:38 Go to previous message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
All views are described in Database Reference

Regards
Michel
Previous Topic: Formatting the flat file output using UTL_File
Next Topic: Sum With Select problem (merged 4)
Goto Forum:
  


Current Time: Sat Dec 10 13:06:48 CST 2016

Total time taken to generate the page: 0.10295 seconds