如何从 PL/SQL 函数或过程内部访问 Oracle 系统表？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2602536/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
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):
我正在尝试从函数内访问 Oracle 元数据表中的信息。例如（有意简化）：
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:
当我尝试在 sqlplus 进程中执行此命令时，出现以下错误：
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:
我知道用户有权访问该表，因为当我从同一个 sqlplus 进程执行以下命令时，它显示了预期的信息：
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?
有什么我需要做的不同吗？
采纳答案by Peter Lang
Make sure that SELECTis 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.
确保这SELECT不仅是通过角色授权，而且用户实际上拥有授权。按角色授予的权限不适用于包。请参阅asktom.oracle.com 上的这篇文章。
Also, try sys.dba_data_filesinstead of dba_data_files.
另外，尝试sys.dba_data_files代替dba_data_files.
回答by Marlon Leite de Albuquerque
Specify WITH GRANT OPTION to enable the grantee to grant the object privileges to other users and roles.
指定 WITH GRANT OPTION 以允许被授予者将对象权限授予其他用户和角色。
GRANT SELECT ON dba_data_files TO YOUR_USER WITH GRANT OPTION;
回答by Klaus Byskov Pedersen
Have you tried prefixing the table name with sys.as in
您是否尝试过在表名前加上sys.as in
FROM sys.dba_data_files
回答by Alijon
For selecting data from dba_data_files, grant select from SYS user to USER. Example:
要从 中选择数据dba_data_files，请将选择从 SYS 用户授予 USER。例子：
GRANT SELECT ON dba_data_files TO YOUR_USER;
After that recompile your Procedure.
之后重新编译您的程序。
