Can't select from dba_tab_cols from within stored procedure (PL/SQL)

Question

I'm trying to SELECT from the dba_tab_cols view from within a stored procedure. It's not working and I don't know why.

If I execute the following SQL as a query:

SELECT t.data_type FROM dba_tab_cols t
WHERE 
    t.table_name = 'ACCOUNTTYPE' AND 
    t.column_name = 'ACCESSEDBY';

it works fine. However if I copy it into a stored procedure like so:

SELECT t.data_type INTO dataType FROM dba_tab_cols t
WHERE
    t.table_name = 'ACCOUNTTYPE' AND 
    t.column_name = 'ACCESSEDBY';

I get the error message "PL/SQL: ORA-00942: table or view does not exist" and the editor highlights dba_tab_cols while trying to compile. The same db user is being used in both cases.

dataType is declared as: dataType varchar2(128);

PL/SQL (Oracle 9)

Anybody know the issue?

Answer 1

It's most likely a priviledges issue. Is the permission to access dba_tab_columns via a role or is it a direct select grant to your user? Priviledges granted via Roles aren't available in SPROCS.

A quick look on google suggests using all_tab_cols instead and seeing if that table has the required info you need.