sqloracle-databaseplsqlora-00942

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

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?

Solution

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