ORA-00942：表或视图不存在（在单独的sql时有效，但在oracle函数内无效）
当我有像 select * from table1 这样的 sql 语句时，它工作得很好，但是一旦我将它放入函数中，我就会得到：
ORA-00942: table or view does not exist
如何解决这个问题？
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:
ORA-00942: table or view does not exist
How to solve this?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（5）
从 table1 中进行选择的权限很可能已被授予某个角色，并且该角色已被授予您。即使用户已被授予角色，授予角色的权限也不可用于用户编写的 PL/SQL。
对于已被授予 sys 拥有的对象的 dba 角色的用户，您会经常看到这种情况。具有 dba 角色的用户将能够执行 SELECT * from V$SESSION 等操作，但无法编写包含 SELECT * FROM V$SESSION 的函数。
解决方法是直接向用户授予对相关对象的显式权限，例如，在上述情况下，SYS 用户必须
GRANT SELECT ON V_$SESSION TO MyUser;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.
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 includesSELECT * 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;您可以查看以下几件事。根据您的问题，函数所有者似乎与表所有者不同。
1) 通过角色授予：为了在另一个用户的对象上创建存储过程和函数，您需要直接访问对象（而不是通过角色访问）。
2）
如果您在模式 A 中创建了表并在模式 B 中创建了函数，则应查看 Oracle 的调用者/定义者权限概念，以了解可能导致问题的原因。
http://download.oracle.com/docs/cd/B19306_01/appdev.102/b14261/subprograms.htm#LNPLS00809
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).
2)
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.
http://download.oracle.com/docs/cd/B19306_01/appdev.102/b14261/subprograms.htm#LNPLS00809
确保该函数与表位于相同的数据库架构中。
Make sure the function is in the same DB schema as the table.
要么您没有该架构/表的权限，要么表确实存在。
大多数情况下，如果您在存储过程中使用其他架构表，就会出现此问题。
例如。如果您从用户/模式 ABC 运行存储过程，并且在同一 PL/SQL 中，存在来自用户/模式 XYZ 的表。在这种情况下，ABC应该具有GRANT，即XYZ表的权限
Grant All On To ABC；
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
Grant All On To ABC;
一个非常简单的解决方案是添加数据库名称和表名称，例如如果您的数据库名称是 DBMS，表是 info，那么它将是 DBMS.info< /code> 任何查询。
如果您的查询是，
它可能会显示错误，但
它不会，因为现在实际上找到了您的表。
A very simple solution is to add the database name with your table name like if your DB name is
DBMSand table isinfothen it will beDBMS.infofor any query.If your query is
it might show an error but
it doesn't because now actually your table is found.