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

Question

I am trying to access information from an Oracle metadata 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 the 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 that 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; 

Result:

 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 a grant through the role , but also that the user has a grant. Role grants do not apply to packages. See the post at asktom.oracle.com .

Alternatively, try sys.dba_data_files instead of dba_data_files .

Answer 2

Have you tried the sys. table name prefix sys. , how in

 FROM sys.dba_data_files 

Answer 3

Specify WITH GRANT OPTION to allow the recipient to grant privileges to objects to other users and roles.

 GRANT SELECT ON dba_data_files TO YOUR_USER WITH GRANT OPTION; 

Answer 4

To select data from dba_data_files select the option to select the user SYS for user USER. Example:

 GRANT SELECT ON dba_data_files TO YOUR_USER; 

After that recompile your procedure.