Re: Help! PL/SQL-->"select ... from dba_tab_columns"

Zhang Anlin wrote:
>
> A "select ... from dba_tab_columns" works well when executed alone, but when it was put
> in a PL/SQL script and compiled, it cause an compiling error.
>
> Why???
>
> Is the system table "dab_tab_columns" special from other user tables?
>
> Thanks.
> Alex Zhang.

Your account probably has select access on DBA_TAB_COLUMNS through a role. A stored procedure ignores any security (object or system) that is granted to you through a role. You only get privileges granted to you explicitly, or that you get inherently because you own objects.

So, to be able to select from DBA_TAB_COLUMNS in a stored procedure, the SYS account has to grant SELECT on DBA_TAB_COLUMNS directly to your account.

-- 
Bob Swisshelm
Eli Lilly and Company
swisshelm_at_lilly.com