如何从PL/SQL函数或过程内部访问Oracle系统表? [英] How to access Oracle system tables from inside of a PL/SQL function or procedure?

问题描述

我正在尝试从函数内部访问Oracle元数据表中的信息.例如(有意简化):

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;
/

当我尝试在sqlplus进程中执行此命令时，出现以下错误:

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

我知道用户有权访问该表，因为当我从同一sqlplus进程执行以下命令时，它将显示预期的信息:

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;

这将导致:

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

我需要做些别的事情吗?

Is there something I need to do differently?

推荐答案

确保不仅SELECT不仅是通过角色授予的，而且用户实际上已经获得了授予.按角色授予不适用于软件包.参见发布在asktom.oracle上的信息.com .

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.

此外，请尝试使用sys.dba_data_files而不是dba_data_files.

Also, try sys.dba_data_files instead of dba_data_files.

这篇关于如何从PL/SQL函数或过程内部访问Oracle系统表?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！