How to access Oracle system tables from inside of a PL/SQL function or procedure?

Question

I am trying to access information from an Oracle meta-data table from within a function. For example (purposefully simplified):

CREATE OR REPLACE PROCEDURE MyProcedure
IS
    users_datafile_path VARCHAR2(100);
BEGIN
    SELECT file_name INTO users_datafile_path
        FROM dba_data_files
        WHERE tablespace_name='USERS'
        AND rownum=1;
END MyProcedure;
/

When I try to execute this command in an sqlplus process, I get the following errors:

LINE/COL ERROR
-------- -----------------------------------------------------------------
5/5      PL/SQL: SQL Statement ignored
6/12     PL/SQL: ORA-00942: table or view does not exist

I know the user has access to the table, because when I execute the following command from the same sqlplus process, it displays the expected information:

SELECT file_name
    FROM dba_data_files
    WHERE tablespace_name='USERS'
    AND rownum=1;

Which results in:

FILE_NAME
--------------------------------------------------------------------------------
/usr/lib/oracle/xe/oradata/XE/users.dbf

Is there something I need to do differently?

Answer 1

Make sure that SELECT is not only grantet through a role, but that the user actually has the grant. Grants by roles do not apply to packages. See this post at asktom.oracle.com.

Also, try sys.dba_data_files instead of dba_data_files.

Answer 2

Specify WITH GRANT OPTION to enable the grantee to grant the object privileges to other users and roles.

GRANT SELECT ON dba_data_files TO YOUR_USER WITH GRANT OPTION;

Answer 3

Have you tried prefixing the table name with sys. as in

FROM sys.dba_data_files

Answer 4

For selecting data from dba_data_files, grant select from SYS user to USER. Example:

GRANT SELECT ON dba_data_files TO YOUR_USER;

After that recompile your Procedure.