Re: all_tab_columns in a stored procedure?

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.

rajXesh_at_hotmail.com (RK) wrote in message news:<548b9514.0207290447.aa13e91_at_posting.google.com>...
> 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 9.0.1.0.1 on Solaris
>
> -- rajXesh
Received on Mon Jul 29 2002 - 12:56:47 CDT