all_tab_columns in a stored procedure?

From: RK <rajxesh_at_hotmail.com>
Date: 26 Jul 2002 12:04:39 -0700
Message-ID: <86c750f4.0207261104.6585d054@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 Fri Jul 26 2002 - 14:04:39 CDT