Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Usenet -> c.d.o.server -> Re: all_tab_columns in a stored procedure?

Re: all_tab_columns in a stored procedure?

From: RK <>
Date: 30 Jul 2002 05:49:26 -0700
Message-ID: <>

Thanks, that answers my question. I didnt realize ( or was too dumb to think about it) that ALL_TAB_COLUMNS is dynamic. (Jusung Yang) wrote in message news:<>...
> Just to be clear, let me rephrase your question. Your question is :
> why can I see only my tables in the view ALL_TAB_COLUMNS, not other
> tables in other schemas - even though I have access to these tables
> and I can see them in the ALL_TAB_COLUMNS from SQL*PLUS.
> Remember, the definition of ALL_TAB_COLUMNS is :
> the columns of all tables, views, and clusters accessible to the user.
> It is not a static table, its content will change according to your
> environment. The objects you have access to when running a SP are
> different from, a subset of really, those you do in a SQL*PLUS
> environment. Therefore the different content of ALL_TAB_COLUMNS.
> You can do a simple experiment to help you clarify this. Do a
> select count(1),object_type from all_objects group by object_type;
> from SQL*PLUS and from a SP, and compare the results. The difference
> could be huge - depending on how many objects are granted to you via
> roles.
> (RK) wrote in message news:<>...
> > Yes, I know that roles are disabled in Stored Procedures, BUT what I
> > wanted to know is why a query on ALL_TAB_COLUMNS in a SP works fine
> > when I specify the tables that I own, not for any other tables even
> > though I can see the results in SQL*Plus.
> >
> > I expect a query on ALL_TAB_COLUMNS, either to behave like it does in
> > SQL*PLus or Not to give any results.
> >
> > Why is ALL_TAB_COLUMNS behaving like USER_TAB_COLUMNS
> >
> > Oracle on Solaris
> >
> > -- rajXesh
Received on Tue Jul 30 2002 - 07:49:26 CDT

Original text of this message