Oracle '必须声明标识符 myschema.mytable'
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/970875/
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
Oracle 'identifier myschema.mytable must be declared'
提问by Sam
Trying to run some sql in a pl/sql procedure.
试图在 pl/sql 过程中运行一些 sql。
Select field from schema.view;
从 schema.view 中选择字段；
I get a compile error
我收到编译错误
Error(22,18): PLS-00201: identifier 'schema.view' must be declared
错误（22,18）：PLS-00201：必须声明标识符“schema.view”
From the error, it seems that my user does not have access to the table. I can run the same statement in a query window.
从错误来看，我的用户似乎无权访问该表。我可以在查询窗口中运行相同的语句。
Is there a permission I need to grant?
我需要授予权限吗？
Thanks!
谢谢！
回答by Khb
You need to grant the user running the procedure explicit permission on the table/view. Granting it through a role will not work.
您需要授予用户在表/视图上运行该过程的显式权限。通过角色授予它是行不通的。
Regards K
问候 K
回答by Steve Broberg
Yes. Oracle doesn't consider permissions that you have from role membership when you are executing procedures, so it is likely that the user who owns the procedure was granted access to schema.view via a role. You need to explicitly grant permissions for that object to the owner of the procedure.
是的。当您执行过程时，Oracle 不会考虑您从角色成员资格中获得的权限，因此拥有该过程的用户很可能被授予通过角色访问 schema.view 的权限。您需要将该对象的权限显式授予该过程的所有者。
回答by spencer7593
The PLS-00201 exception seems a bit unusual to me, for the "privileges granted through a role not available in a stored procedure" problem. As Steve Broberg and Khb have already pointed out, granting privileges directly to a user will resolve exception
PLS-00201 异常对我来说似乎有点不寻常，因为“通过存储过程中不可用的角色授予的特权”问题。正如 Steve Broberg 和 Khb 已经指出的那样，直接向用户授予权限将解决异常
ORA-00942: table or view does not exist.
(That's the exception I normally see when compiling a stored procedure, when the statement works outside the stored procedure, and i find that privileges are granted through roles.)
（这是我在编译存储过程时通常看到的异常，当语句在存储过程之外工作时，我发现权限是通过角色授予的。）
What's kind of peculiar is that the exception you are seeing is a PLS-00201(That has me puzzled.)
有点奇怪的是，您看到的例外是PLS-00201（这让我感到困惑。）
Another workaround to the ORA-942 "no privileges through roles" issue is to define the procedure with invoker rights and use dynamic SQL:
ORA-942“无权限通过角色”问题的另一个解决方法是定义具有调用者权限的过程并使用动态 SQL：
create procedure foo authid current_user
is
ln_cnt number;
begin
execute immediate 'select cnt(1) from schema.view' into ln_cnt;
end;
/
I don't think this is the best approach (it has its own issues) but it is a workaround.
我不认为这是最好的方法（它有自己的问题），但它是一种解决方法。
http://download.oracle.com/docs/cd/B28359_01/appdev.111/b28370/dynamic.htm
http://download.oracle.com/docs/cd/B28359_01/appdev.111/b28370/dynamic.htm
