Re: Obtaining advanced views metadata information from Oracle.

From: DA Morgan <damorgan_at_x.washington.edu>
Date: Wed, 23 Mar 2005 14:20:30 -0800
Message-ID: <1111616221.49811@yasure>

Andy Hassall wrote:

> On 23 Mar 2005 13:11:30 -0800, "Seba" <orav.20.sebastianpy_at_spamgourmet.com>
> wrote:
>
>

>>For Oracle 9i (or 10i) I need to get advanced metadata information.
>>
>>I know how to get the DESC information of a view that will give me the
>>columns, types, etc. But what I'm looking for is the *tables* that have
>>the source information for the view.
>>
>>i.e. : if the following view was defined in my database:
>>
>>create view my_view as
>>select parent_id, son_id

>

>>from parent A, son B

>

>>where A.parent_id = B.parent_id
>>
>>Is there any way that starting from my_view I can get the information
>>that parent and son are the tables that are actually the source tables
>>for the data that make up the view?

>
>
> all_dependencies holds this sort of information. Start from the view and do a
> hierarchical query on the tree of dependencies, then pick the tables from that
> lot.
>
> select referenced_owner, referenced_name
> from (
> select referenced_owner, referenced_type, referenced_name
> from all_dependencies
> connect by prior referenced_owner = owner
> and prior referenced_type = type
> and prior referenced_name = name
> start with owner = 'VIEW_OWNER'
> and name = 'VIEW_NAME'
> )
> where referenced_type = 'TABLE'
>
> That should cope with views on views, and if you're calling functions then
> it'd get accessed tables from there too (excluding dynamic SQL).
>
>

>>Another *different* question is how can I get the code that generates
>>the view (the select query above). But please note that this will *not*
>>solve my problem, I am working in a programmatic environment so I can't
>>"parse" a bunch of SQL to find out which ones are the source tables.

>
>
> all_views.text
>
> http://download-west.oracle.com/docs/cd/B14117_01/server.101/b10755/statviews_1202.htm#sthref1502

http://www.psoug.org
click on Morgan's Library
click on DBMS_METADATA

-- 
Daniel A. Morgan
University of Washington
damorgan_at_x.washington.edu
(replace 'x' with 'u' to respond)

Received on Wed Mar 23 2005 - 16:20:30 CST