This is one of those FAQs. Roles are disabled within stored
procedures. In SPs, you may not have access to some objects granted to
you thru roles - which makes querying ALL* views within SPs somewhat
dangerous. Do a direct grant if you have to.
rajxesh_at_hotmail.com (RK) wrote in message news:<86c750f4.0207261105.7e24b86a_at_posting.google.com>...
> 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
Received on Sat Jul 27 2002 - 02:10:25 CDT
