oracle 无法从存储过程 (PL/SQL) 中的 dba_tab_cols 中进行选择
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/803618/
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
Can't select from dba_tab_cols from within stored procedure (PL/SQL)
提问by JJ.
I'm trying to SELECT from the dba_tab_cols view from within a stored procedure. It's not working and I don't know why.
我正在尝试从存储过程中的 dba_tab_cols 视图中进行 SELECT。它不起作用，我不知道为什么。
If I execute the following SQL as a query:
如果我执行以下 SQL 作为查询：
SELECT t.data_type FROM dba_tab_cols t
WHERE
t.table_name = 'ACCOUNTTYPE' AND
t.column_name = 'ACCESSEDBY';
it works fine. However if I copy it into a stored procedure like so:
它工作正常。但是，如果我将其复制到这样的存储过程中：
SELECT t.data_type INTO dataType FROM dba_tab_cols t
WHERE
t.table_name = 'ACCOUNTTYPE' AND
t.column_name = 'ACCESSEDBY';
I get the error message "PL/SQL: ORA-00942: table or view does not exist" and the editor highlights dba_tab_cols while trying to compile. The same db user is being used in both cases.
我收到错误消息“PL/SQL：ORA-00942：表或视图不存在”，并且编辑器在尝试编译时突出显示 dba_tab_cols。在这两种情况下都使用相同的 db 用户。
dataType is declared as: dataType varchar2(128);
dataType 声明为： dataType varchar2(128);
PL/SQL (Oracle 9)
PL/SQL (Oracle 9)
Anybody know the issue?
有人知道这个问题吗？
回答by Eoin Campbell
It's most likely a priviledges issue. Is the permission to access dba_tab_columnsvia a role or is it a direct select grant to your user? Priviledges granted via Roles aren't available in SPROCS.
这很可能是一个特权问题。是dba_tab_columns通过角色访问权限还是直接选择授予您的用户？通过角色授予的特权在 SPROCS 中不可用。
A quick look on google suggests using all_tab_colsinstead and seeing if that table has the required info you need.
快速浏览谷歌建议all_tab_cols改为使用并查看该表是否具有您需要的所需信息。
回答by devio
To add to Eoin's answer:
添加到 Eoin 的答案中：
For most people, it comes as a surprise that the user cannot select the table from within a procedure if he has not been granted the select right directly (as opposed to through the role)
If table user tries to compile this procedure, he gets a ORA-00942 although this table certainly exists and he was granted the right to select this table. The problem is that procedures don't respect roles; only directly granted rights are respected. So, that means that table owner has to regrant the right to select:
对于大多数人来说，如果用户没有被直接授予选择权限（而不是通过角色），那么他无法从过程中选择表，这让大多数人感到惊讶
如果表用户试图编译这个过程，他会得到一个 ORA-00942，尽管这个表确实存在并且他被授予选择这个表的权利。问题是程序不尊重角色；只尊重直接授予的权利。因此，这意味着表所有者必须重新授予选择权：
回答by Nathan Koop
I don't have oracle installed, but perhaps dataType is a reserved word. I'd try something else.
我没有安装 oracle，但也许 dataType 是一个保留字。我会尝试别的东西。
