Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> all_tab_columns in a stored procedure?
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;
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