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

Question

Hi. 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

I don't have oracle installed, but perhaps dataType is a reserved word. I'd try something else.

Answer 2

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.

Answer 3

To add to Eoin's answer:

For most people, it comes as a surprise that the user cannot select the table from within a procedure if he has not been granted the select right directly (as opposed to through the role)

If table user tries to compile this procedure, he gets a ORA-00942 although this table certainly exists and he was granted the right to select this table. The problem is that procedures don't respect roles; only directly granted rights are respected. So, that means that table owner has to regrant the right to select:

http://www.adp-gmbh.ch/ora/err/ora_00942.html