Obtaining advanced views metadata information from Oracle.

From: Seba <orav.20.sebastianpy_at_spamgourmet.com>
Date: 23 Mar 2005 13:11:30 -0800
Message-ID: <1111612290.770488.66420@z14g2000cwz.googlegroups.com>

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?

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.

What I need is a result set that contains the table names of 'parent' and 'son' (referring to the example). Ideally, something like:

SQL> [magic query involving my_view];

TABLE

---

parent
son

What is the magic query?

If it is not possible to get this information, please let me know.

Any help would be greatly appreciated. :)

S Received on Wed Mar 23 2005 - 15:11:30 CST