SQL 遇到错误：表或视图不存在
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/4923408/
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
Facing an error : table or view does not exist
提问by Vijay Balkawade
I am using insert statement and trying to insert data into the database table. I am using stored procedures.
But I am getting this error while doing so.
我正在使用插入语句并尝试将数据插入到数据库表中。我正在使用存储过程。
但是我在这样做时遇到了这个错误。
Message: ORA-00942: table or view does not exist ORA-06512
消息：ORA-00942：表或视图不存在 ORA-06512
I checked if the tables/stored procedures are present or not and everything is in place. Also there is no typo in table names or in sp. If I run the part of SP from query editor it works fine but when I execute the entire SP it throws an error.
我检查了表/存储过程是否存在并且一切都到位。表名或 sp 中也没有拼写错误。如果我从查询编辑器运行 SP 的一部分，它工作正常，但是当我执行整个 SP 时，它会引发错误。
I tried the steps provided by Stephen but since I have logged in with the same user/owner when I run Grant command it gives me an error saying 'Cannot Grant/revoke on own'.
One more addition to this. I have a stored procedure SP1 in which I am using a select statement as
我尝试了 Stephen 提供的步骤，但由于我在运行 Grant 命令时使用相同的用户/所有者登录，因此出现错误，提示“无法自行授予/撤销”。
还有一个补充。我有一个存储过程 SP1，我在其中使用 select 语句作为
Select a from table_name where condition;
When I execute this seperately, it returns me some results. But when I execute sp it gives an error at the same line where it is written.
当我单独执行此操作时，它会返回一些结果。但是当我执行 sp 时，它会在写入的同一行出现错误。
Can anyone help me out to resolve this issue. I am using SQL +.
Thanks in advance
Vijay
谁能帮我解决这个问题。我正在使用 SQL +。
提前致谢 维杰
回答by Stephanie Page
Justin's answer is correct but let me expand a bit.
贾斯汀的回答是正确的，但让我扩展一下。
Everyone who said that the table doesn't exist didn't read your whole post. Since you are able to:
说表格不存在的每个人都没有阅读您的整篇文章。由于您能够：
If I run the part of SP from query editor it works fine
如果我从查询编辑器运行 SP 的一部分，它工作正常
Obviously the table is there. Obviously you have some access to it. Otherwise this wouldn't work when it clearly does.
显然桌子在那里。显然，您可以访问它。否则，当它明显起作用时，这将不起作用。
but when I execute the entire SP it throws an error.
但是当我执行整个 SP 时，它会引发错误。
This is because Oracle distinguishes between permissions granted directly and those granted via a role.
这是因为 Oracle 区分了直接授予的权限和通过角色授予的权限。
Say I do this:
说我这样做：
Create Table TABLE_A
Create Role READ_ONLY
Grant Select on TABLE_A to READ_ONLY
Grant READ_ONLY to VIJAY
In a SQL Window/prompt you could query that table without issue. So now you need to create a view
在 SQL 窗口/提示中，您可以毫无问题地查询该表。所以现在你需要创建一个视图
Create VIJAY.VIEW_A as SELECT * FROM TABLE_A
You'll get the error that TABLE_A does exist. Because a view is compiled, like a procedure it runs without any roles. Since it runs without the READ_ONLY role, it's blind to the fact that TABLE_A exists. Now what I need to do is
你会得到 TABLE_A 确实存在的错误。因为视图是编译的，就像它在没有任何角色的情况下运行的过程一样。由于它在没有 READ_ONLY 角色的情况下运行，因此对 TABLE_A 存在的事实视而不见。现在我需要做的是
Grant Select on TABLE_A to VIJAY.
Now that you have a direct permission, you can compile a view or procedure/package that uses that table.
现在您拥有直接权限，您可以编译使用该表的视图或过程/包。
回答by Justin Cave
Does the table exist in the schema where the stored procedure exists? If not, the simplest explanation is that the owner of your procedure has been granted access to the table via a role not via a direct grant. A definer's rights stored procedure needs to have direct access to the objects it accesses. A quick way to test this is to disable roles for the session, i.e.
该表是否存在于存储过程所在的架构中？如果没有，最简单的解释是您的过程的所有者已通过角色而不是直接授权获得对表的访问权限。定义者权限存储过程需要直接访问它访问的对象。测试这个的一个快速方法是禁用会话的角色，即
SQL> set role none;
SQL> <<execute your query>>
If that generates the error, the problem is the lack of a direct grant.
如果这会产生错误，则问题在于缺少直接授权。
回答by Dennis Benzinger
In Oracle you can choose if the stored procedure is executed with the rights of the invoker or the definer: http://download.oracle.com/docs/cd/E11882_01/appdev.112/e17126/subprograms.htm#i18574
在 Oracle 中，您可以选择是否使用调用者或定义者的权限执行存储过程：http: //download.oracle.com/docs/cd/E11882_01/appdev.112/e17126/subprograms.htm#i18574
Check if the AUTHID property of the stored procedure is correct and if the resulting user has appropriate permissions.
检查存储过程的 AUTHID 属性是否正确以及生成的用户是否具有适当的权限。
回答by Paddy
Well, put very simply, the table that you are trying to insert data into does not exist in the database you are connected to. You need to check both those things (i.e. what are you connected to, and is the table there and accessible for the user context you are using).
好吧，简而言之，您尝试向其中插入数据的表在您连接的数据库中不存在。您需要检查这两件事（即您连接到什么，以及您正在使用的用户上下文是否可以访问该表）。
回答by Prahalad Deshpande
As Joe Stefanelli said .. there are a lot of possibilities for the error being shown here.
正如 Joe Stefanelli 所说.. 这里显示的错误有很多可能性。
Check whether:
检查是否：
- You are connecting to the correct Oracle Instance.
- You have permissions to query or perform processing on table that you are referencing in your query.
- There is a difference between ordinary select statements and procedures. Procedures in oracle do not respect the roles assigned to a user; rather the permission needs to be explicitly granted to the user. For more information read the following linkORA-00942
- 您正在连接到正确的 Oracle 实例。
- 您有权查询或对您在查询中引用的表执行处理。
- 普通的 select 语句和过程是有区别的。oracle 中的过程不尊重分配给用户的角色；而是需要明确授予用户权限。有关更多信息，请阅读以下链接ORA-00942
