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

Posted by mjumbewu on Stack Overflow See other posts from Stack Overflow or by mjumbewu
Published on 2010-04-08T18:43:57Z Indexed on 2010/04/08 18:53 UTC
Read the original article Hit count: 149

Filed under:
|
|

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?

© Stack Overflow or respective owner

Related posts about Oracle

Related posts about plsql