Re: complex view-architecture

Oliver Stratmann wrote:

> hello TurkBear!
>
> thanks for your help.
>
> if there is more than one view involved, than it would be nice to get a
> description like
>
> view_layer3.sp1 --> view_layer2.sp6 --> view_layer1.sp9 -->
> source_tab.source_col
>
> i think this is a nice feature for documenting complex view-constructs.
>
> selecting the code helps just for one dependency. (user_views)
>
> a combination with the user_dependencies (hierarchiecal select) would get
> the whole view-construction.
>
> but the presentation of the viewcode(s) selected this way is not clear to me
> and doesn't look simple.
>
> Bye!
> Oli
>
> "TurkBear" <john.greco_at_dot.state.mn.us> schrieb im Newsbeitrag
> news:ms3j3v4k2nd188ck4va7ejpvsn8hg0m4b4_at_4ax.com...
> >
> >
> > Unless I am missing what you need why isn't this enough?
> > --------------------------------------------------------------------------
> ---------------------------------------
> >
> > SQL> create view test_v as
> > 2 select a.dept_nbr AS dpt1,b.dept_nbr AS dpt2
> > 3 from
> > 4 hr_public a,hr_cur_rptng b
> > 5 ;
> >
> > View created.
> >
> > SQL> set long 1000
> > SQL> select text from user_views where view_name = 'TEST_V';
> >
> > TEXT
> > --------------------------------------------------------------------------
> ------
> > select a.dept_nbr AS dpt1,b.dept_nbr AS dpt2
> > from
> > hr_public a,hr_cur_rptng b
> >
> > --------------------------------------------------------------------------
> ------------------------
> >
> > "Oliver Stratmann" <ostratmann_at_zeb.de> wrote:
> >
> > >Hello Daniel,
> > >
> > >I'm not sure if I understand Your answer.
> > >Do You think that all the required information is included in the view
> > >USER_VIEWS?
> > >As far as I understand this the USER_VIEWS shows the view-code. But there
> is
> > >still a lot of work to do in order to get the column-dependencies.
> > >
> > >e.g. there is a view
> > >create or replace view test
> > >as
> > >select a.sp1 as test_sp1,
> > > b.sp1 as test_sp2,
> > > ....
> > >from tab1 a,
> > > tab2 b
> > >
> > >the column-dependencies would be in this case:
> > >"
> > >tab1.sp1 --> test.test_sp1
> > >tab2.sp1 --> test.test_sp2
> > >...
> > >"
> > >
> > >I do not understand how I get this information directly out of the
> > >user_views, sorry.
> > >Can you or anyone else help me?
> > >
> > >Bye,
> > >Oli
> > >
> > >
> > >"DA Morgan" <damorgan_at_exesolutions.com> schrieb im Newsbeitrag
> > >news:3E371AE6.D8F517F7_at_exesolutions.com...
> > >> Oliver Stratmann wrote:
> > >>
> > >> > hello all,
> > >> >
> > >> > is there a tool which shows the column-dependencies of view-columns.
> if
> > >yes,
> > >> > over how many views does this tool work?
> > >> > This functionality is needed for documentation, is there any other
> way
> > >than
> > >> > checking every single view-code?
> > >> >
> > >> > Thanks in advance!
> > >> >
> > >> > Bye,
> > >> > Oli
> > >>
> > >> Do a describe on user_views.
> > >>
> > >> You will see that which you seek.
> > >>
> > >> Daniel Morgan
> > >>
> > >
> >

There is nothing simple about what you have asked.

Part of what you asked was where the source code for views is stored which is why I pointed you to user_views.

Keep in mind that views may have INSTEAD OF triggers which means that there is not necessarily a mapping from the SELECT statement that creates a view to the tables it may affect.

Daniel Morgan Received on Thu Jan 30 2003 - 23:26:55 CET