Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: Obtaining a views' column origin
Helmut Vogel <Helmut.Vogel_at_web.de> wrote in
news:3d25ece3$1_at_netnews.web.de:
> Rene Nyffenegger <rene.nyffenegger_at_gmx.ch> wrote:
>>> Helmut Vogel <Helmut.Vogel_at_web.de> wrote in >>> news:3d256ad6$1_at_netnews.web.de: >>> >>>> Ok, I can read the view's definition in xxx_views. But there's no >>>> mapping between the columns of a view and its underlying table in >>>> xxx_tab_columns. What I need is a function like >>>> GetBaseTablename(view,column). Thank you >>>> Helmut >>>> >>>> >>>> Connor McDonald <connor_mcdonald_at_yahoo.com> wrote: >>>>>Helmut Vogel wrote: >>>>>> >>>>>> Hi, >>>>>> >>>>>> i need to obtain the underlying column and table name of a view's >>>>>> column. Can I get this info from the data dictionary? >>>>>> I use Oracle Server 8.1.6.1.0 >>>>>> Thanx 4 help! >>>>>> >>>>>> Helmut >>>>>> -- >>>>>> __________________________________________________________ >>>>>> News suchen, lesen, schreiben mit http://newsgroups.web.de >>>>> >>>>>xxx_VIEWS has the definition >>>>> >>>>>xxx_TAB_COLUMNS has the column mapping >>>>> >>>>>hth >>>>>connor >>>>>-- >>>>>============================== >>>>>Connor McDonald >>>>> >>>>>http://www.oracledba.co.uk >>>>> >>>>>"Some days you're the pigeon, some days you're the statue..." >>>> >>> >>> I don't think it exists. It sounds like you want to input the view >>> name and the column, and have the function query the view >>> description and somehow parse out the select statement for the view >>> to just give you the relevant info for that column. But views can >>> sometimes have very complex queries, with subqueries, etc... that >>> would make it very hard to do something like that automatically. >>> >>> Anyone disagree with me? >> >> >>No, definitely not! >> >>create view x as select a.y+b.z as q from some_table a, another table >>b where >>a.id = b.id; >> >>would get GetBaseTablename(x,q) in troubles. >> >>Rene >> >> >> >> >> >>-- >>Recherchen im schweizerischen Handelsregister: >>http://www.adp-gmbh.ch/SwissCompanies/Search.php3 >>
But to create the table you want, you HAVE to parse/compile the view's text. It's the only way you could build a reference from the view's column to the underlying table's column. And as Rene showed us, that isn't likely to happen. You'd have to return NULL for EVERY time the column was created from more than a single column, making the whole exercise pointless.
-- Jason Baugher Virtual Adept Professional Consulting Services 1406 Adams St. Quincy, IL 62301 (217) 221-5406 http://baugher.pike.il.us/virtualadept jason_at_baugher.pike.il.usReceived on Fri Jul 05 2002 - 16:03:41 CDT