How to access Oracle system tables from inside of a PL/SQL function or procedure?
- by mjumbewu
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?
