Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Obtaining a views' column origin

Re: Obtaining a views' column origin

From: Jason Baugher <jason_at_baugher.pike.il.us>
Date: Fri, 05 Jul 2002 21:03:41 GMT
Message-ID: <Xns9242A3628B018jasonbaugherpikeilus@209.242.76.10>


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
>>

>
>
> In such a case the function should return NULL, that's obvious. To
> parse the view's text is not what i want. Isn't the information stored
> anywhere in the system tables?
>
>
> Helmut
>
>
>

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.us
Received on Fri Jul 05 2002 - 16:03:41 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US