ORA-00942: 表或视图不存在（在单独的 sql 时有效，但在 oracle 函数内无效）
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/6669575/
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
ORA-00942: table or view does not exist (works when a separate sql, but does not work inside a oracle function)
提问by Victor
When I have a sql statement like select * from table1, it works great, but as soon as I put it into a function, I get:
当我有一个像 的 sql 语句时select * from table1，它工作得很好，但是一旦我将它放入一个函数中，我就会得到：
ORA-00942: table or view does not exist
How to solve this?
如何解决这个问题？
采纳答案by Rajesh Chamarthi
There are a couple of things you could look at. Based on your question, it looks like the function owner is different from the table owner.
有几件事你可以看看。根据您的问题，函数所有者似乎与表所有者不同。
1) Grants via a role : In order to create stored procedures and functions on another user's objects, you need direct access to the objects (instead of access through a role).
1) 通过角色授予：为了在另一个用户的对象上创建存储过程和函数，您需要直接访问对象（而不是通过角色访问）。
2)
2)
By default, stored procedures and SQL methods execute with the privileges of their owner, not their current user.
默认情况下，存储过程和 SQL 方法以其所有者的权限执行，而不是当前用户的权限。
If you created a table in Schema A and the function in Schema B, you should take a look at Oracle's Invoker/Definer Rights concepts to understand what might be causing the issue.
如果您在架构 A 中创建了一个表，在架构 B 中创建了函数，您应该查看 Oracle 的调用者/定义者权限概念以了解可能导致问题的原因。
http://download.oracle.com/docs/cd/B19306_01/appdev.102/b14261/subprograms.htm#LNPLS00809
http://download.oracle.com/docs/cd/B19306_01/appdev.102/b14261/subprograms.htm#LNPLS00809
回答by Steve Broberg
There's a strong chance that the privileges to select from table1 have been granted to a role, and the role has been granted to you. Privileges granted to a role are not available to PL/SQL written by a user, even if the user has been granted the role.
很有可能从 table1 中选择的权限已授予某个角色，并且该角色已授予您。授予角色的权限对用户编写的 PL/SQL 不可用，即使该用户已被授予该角色。
You see this a lot for users that have been granted the dba role on objects owned by sys. A user with dba role will be able to, say, SELECT * from V$SESSION, but will not be able to write a function that includes SELECT * FROM V$SESSION.
对于已被授予 sys 拥有的对象的 dba 角色的用户，您会经常看到这种情况。具有 dba 角色的用户将能够，例如SELECT * from V$SESSION，，但不能编写包含SELECT * FROM V$SESSION.
The fix is to grant explicit permissions on the object in question to the user directly, for example, in the case above, the SYS user has to GRANT SELECT ON V_$SESSION TO MyUser;
解决方法是直接向用户授予有关对象的明确权限，例如，在上述情况下，SYS 用户必须 GRANT SELECT ON V_$SESSION TO MyUser;
回答by Adriano Carneiro
Make sure the function is in the same DB schema as the table.
确保该函数与表位于相同的数据库架构中。
回答by Ravindra Junghare
Either u dont have permission to that schema/table OR table does exist. Mostly this issue occurred if you are using other schema tables in your stored procedures. Eg. If you are running Stored Procedure from user/schema ABC and in the same PL/SQL there are tables which is from user/schema XYZ. In this case ABC should have GRANT i.e. privileges of XYZ tables
要么你没有该架构/表的权限，要么表确实存在。大多数情况下，如果您在存储过程中使用其他架构表，就会出现此问题。例如。如果您从用户/模式 ABC 运行存储过程，并且在同一个 PL/SQL 中有来自用户/模式 XYZ 的表。在这种情况下，ABC 应该具有 GRANT 即 XYZ 表的权限
Grant All On To ABC;
全部授予 ABC；
Select * From Dba_Tab_Privs Where Owner = 'XYZ'and Table_Name = <Table_Name>;
回答by Ankit Neekhra
A very simple solution is to add the database name with your table name like if your DB name is DBMSand table is infothen it will be DBMS.infofor any query.
一个非常简单的解决方案是将数据库名称与您的表名称一起添加，就像您的数据库名称DBMS和表一样，info那么它将DBMS.info用于任何查询。
If your query is
如果您的查询是
select * from STUDENTREC where ROLL_NO=1;
it might show an error but
它可能会显示错误，但是
select * from DBMS.STUDENTREC where ROLL_NO=1;
it doesn't because now actually your table is found.
不会，因为现在实际上找到了您的表。
