Oracle select privilege on DBA_ROLES

Question

I am connected into Oracle 10g database with user "user", when i run a select on DBA_ROLES it displays the result without any problem, but when the select is inside a procedure it returns the error ORA-00942: table or view does not exist

is the user missing some privileges? why is it possible to execute a SELECT but not to include it inside a procedure?

SQL> SELECT COUNT(*) FROM DBA_ROLES;

  COUNT(*)
----------
        18

SQL> CREATE OR REPLACE PROCEDURE tst_role IS 
     v VARCHAR2(100);
BEGIN
        v := '';
        FOR rec IN (SELECT ROLE FROM DBA_ROLES) LOOP
                v := rec.role;
                DBMS_OUTPUT.put_line(v);
        END LOOP;
END;
/
Warning: Procedure created with compilation errors.

SQL> show errors
Errors for PROCEDURE TST_ROLE:

LINE/COL ERROR
-------- -----------------------------------------------------------------
5/13     PL/SQL: SQL Statement ignored
5/30     PL/SQL: ORA-00942: table or view does not exist
6/3      PL/SQL: Statement ignored
6/8      PLS-00364: loop index variable 'REC' use is invalid

Answer 1

within a definer's rights stored procedure, you don't have access to privileges that are granted via a role. This most likely means that whatever Oracle user you are using has been granted access to DBA_ROLES via a role rather than via a direct grant. Most likely, you can ask your DBA to grant your account the SELECT ANY DICTIONARY privilege

GRANT SELECT ANY DICTIONARY
   TO your_oracle_user

You can verify that the problem is, indeed, that the privilege is granted via a role by disabling roles in your session and verifying that you get an error. If you

SQL> set role none;
SQL> SELECT COUNT(*) FROM DBA_ROLES;

I'll wager that you get the same ORA-00942 error.