Re: all_tab_columns in a stored procedure?

RK wrote:

> I have created a procedure
>
> PROCEDURE ColCount ( psTableName IN VARCHAR2) IS
> nCount NUMBER;
> sSQL VARCHAR2(100);
> BEGIN
> sSQL := ' select count(*) ' ||
> ' from all_tab_columns ' ||
> ' where table_name = ''' || upper(psTableName) || '''' ;
> dbms_output.put_line(sSQL);
> EXECUTE IMMEDIATE sSQL INTO nCount;
> dbms_output.put_line(psTableName || ' has ' || to_char(nCount) || '
> columns');
> END;
>
> When I run it with a table_name in my schema (ME.MY_TABLE), it works
> fine (The parameter passed is MY_TABLE)
>
> But, when I run it with a table_name in another schema
> (THEY.THEIR_TABLE, with THEIR_TABLE as the parameter), Count(*)
> returns 0
>
> Note: My UserId has not been granted direct access to
> THEY.THEIR_TABLE, but only thru a role.
>
> If run just the select count(*)from all_tab_columns where table_name =
> ‘THEIR_TABLE’ from SQL*Plus, I get the correct column
> count
>
> So Why does the SELECT on ALL_TAB_COLUMNS in a stored procedure work
> only for my tables, not for any other tables even though I can see the
> result in SQL*Plus
>
> -- rajXesh

You can not use access through a role in a stored procedure: Only a direct object grant.

Daniel Morgan Received on Fri Jul 26 2002 - 18:18:48 CDT