java.sql.SQLSyntaxErrorException: ORA-00942: 表或视图不存在

java.sql.SQLSyntaxErrorException: ORA-00942: table or view does not exist

提问by osimer pothe

when i was trying to read data from Oracle database using the following code i was getting exception

当我尝试使用以下代码从 Oracle 数据库读取数据时出现异常

ResultSet res=stmt.executeQuery("select * from food");

But this table is actually exist in my database when i use this command directly in the command prompt its working fine.And also for one table among the tables in database this code is working fine ,but for other table names its not working properly.So someone please explain why this is happening.

但是当我直接在命令提示符中使用此命令时，该表实际上存在于我的数据库中，它工作正常。而且对于数据库中表中的一个表，此代码工作正常，但对于其他表名，它无法正常工作。所以有人请解释为什么会这样。

java.sql.SQLSyntaxErrorException: ORA-00942: table or view does not exist

    at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:440)
    at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:396)
    at oracle.jdbc.driver.T4C8Oall.processError(T4C8Oall.java:837)
    at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:445)
    at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:191)
    at oracle.jdbc.driver.T4C8Oall.doOALL(T4C8Oall.java:523)
    at oracle.jdbc.driver.T4CStatement.doOall8(T4CStatement.java:193)
    at oracle.jdbc.driver.T4CStatement.executeForDescribe(T4CStatement.java:852)
    at oracle.jdbc.driver.OracleStatement.executeMaybeDescribe(OracleStatement.java:1153)
    at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1275)
    at oracle.jdbc.driver.OracleStatement.executeQuery(OracleStatement.java:1477)
    at oracle.jdbc.driver.OracleStatementWrapper.executeQuery(OracleStatementWrapper.java:392)
    at connecttooracle.ConnectOracle.main(ConnectOracle.java:67)

I am accessing data from food table by Toad . THen why am I getting this error in java ?

我正在通过 Toad 访问食物表中的数据。那么为什么我会在 java 中收到此错误？

My full code is :

我的完整代码是：

public class ConnectOracle {

 public static void main(String[] args) {

  String driver = "oracle.jdbc.driver.OracleDriver"; //

  String serverName = "10.11.201.84";
  String portNumber = "1521";
  String db = "XE";
  String url = "jdbc:oracle:thin:@" + serverName + ":" + portNumber + ":"
    + db; // connectOracle is the data
  // source name
  String user = "ORAP"; // username of oracle database
  String pwd = "ORAP"; // password of oracle database
  Connection con = null;
  ServerSocket serverSocket = null;
  Socket socket = null;
  DataInputStream dataInputStream = null;
  DataOutputStream dataOutputStream = null;

  try {
   Class.forName(driver);// for loading the jdbc driver

   System.out.println("JDBC Driver loaded");

   con = DriverManager.getConnection(url, user, pwd);// for
                // establishing
   // connection
   // with database
   Statement stmt = (Statement) con.createStatement();

   serverSocket = new ServerSocket(8888);
   System.out.println("Listening :8888");

   while (true) {
    try {

     socket = serverSocket.accept();
     System.out.println("Connection Created");
     dataInputStream = new DataInputStream(
       socket.getInputStream());
     dataOutputStream = new DataOutputStream(
       socket.getOutputStream());
     System.out.println("ip: " + socket.getInetAddress());
     // System.out.println("message: " +
     // dataInputStream.readUTF());

     ResultSet res=stmt.executeQuery("select * from food");
     while(res.next()){
      System.out.println(res.getString(1));
     }

    } catch (IOException e) {
     // TODO Auto-generated catch block
     e.printStackTrace();
    }

    if (dataInputStream != null) {
     try {
      dataInputStream.close();
     } catch (IOException e) {
      // TODO Auto-generated catch block
      e.printStackTrace();
     }
    }

    if (dataOutputStream != null) {
     try {
      dataOutputStream.close();
     } catch (IOException e) {
      // TODO Auto-generated catch block
      e.printStackTrace();
     }
    }
   }
  } catch (Exception e) {
   // TODO Auto-generated catch block
   e.printStackTrace();
  }
 }
}

采纳答案by Y.E.

If your table is located under schema A:

如果您的表位于架构 A 下：

select * from A.food

EDIT

编辑

If you can login via TOAD with user ORAP and execute the same query (select * from food) then you definitely have the table in ORAP schema. I see no reason for "select * from ORAP.food" to fail.

如果您可以使用用户 ORAP 通过 TOAD 登录并执行相同的查询（从食物中选择 *），那么您肯定拥有 ORAP 模式中的表。我认为“select * from ORAP.food”没有理由失败。

回答by vssk

Read this. There are all reasons ans solutions.

读这个。有所有的原因和解决方案。

回答by dhruti

Because of this CAUSE,u found this exception. You tried to execute a SQL statement that references a table or view that either does not exist, that you do not have access to, or that belongs to another schema and you didn't reference the table by the schema name.

由于这个原因，你发现了这个异常。您尝试执行引用表或视图的 SQL 语句，该表或视图不存在、您无权访问或属于另一个架构，但您没有通过架构名称引用该表。

回答by Shivkalyan

Follow these steps

按着这些次序

• First login to TOAD using same username and password. If it gives error then your credentials are wrong
• If you are able to login successfully then try this query: SELECT table_name, owner, tablespace_name FROM all_tables;
• You can see your table name is present or not using above query

• 首先使用相同的用户名和密码登录 TOAD。如果它给出错误，那么你的凭据是错误的
• 如果您能够成功登录，请尝试以下查询： SELECT table_name, owner, tablespace_name FROM all_tables;
• 您可以使用上述查询查看您的表名是否存在

回答by Shivkalyan

I also faced the same issue, Resolution is:

我也遇到了同样的问题，解决方法是：

1. Open the hibernate.cfg.xml file
2. Add your POJO class using

1. 打开 hibernate.cfg.xml 文件
2. 使用添加您的 POJO 类

Also check, true update

还要检查，真正的更新

回答by Swapnil G Thaware

This will solve the problem:

这将解决问题：

Open the hibernate.cfg.xmlfile

打开hibernate.cfg.xml文件

Check the mapping properly

<mapping class="com.xyz.abc.java"/>

正确检查映射

<mapping class="com.xyz.abc.java"/>

Also make sure below line:

还要确保以下行：

<property name="hibernate.hbm2ddl.auto">update</property>

<property name="hibernate.hbm2ddl.auto">update</property>

回答by Aliuk

In my case, I solved the problem executing:

就我而言，我解决了执行问题：

grant all on <schema>.<table> to <user>;

回答by Pran Kumar Sarkar

Some of the possible solutions are:

一些可能的解决方案是：

You can create connection in the database by the schema under which database is located.

您可以通过数据库所在的模式在数据库中创建连接。

For example:My original database has admin SYSTEMand then I created an user Toadand under this Toad schema I have created the table Food.

例如：我的原始数据库有 adminSYSTEM，然后我创建了一个用户Toad，在这个 Toad 模式下我创建了表Food。

So I will create connection to the database by:

因此，我将通过以下方式创建与数据库的连接：

Connection conn = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:xe", "Toad", "PasswordOfToad");

Then the query ResultSet res = stmt.executeQuery("select * from Food");will work fine.

然后查询ResultSet res = stmt.executeQuery("select * from Food");将正常工作。

Alternatively you could specify the table by SchemaName.DatabaseName, which in this case is Toadand Foodrespectively.

或者，您可以通过SchemaName.DatabaseName指定表，在本例中分别是Toad和Food。

So the query would look like:

所以查询看起来像：

ResultSet res = stmt.executeQuery("select * from Toad.Food");

回答by Prakash BM

You should mention Schema.table For Example Schema name=xyz then the query would be:select * from xyz.food Try this, this will work

你应该提到 Schema.table 例如 Schema name=xyz 那么查询将是：select * from xyz.food 试试这个，这会起作用

回答by Yash

I have face the same problem but resolved using grant privilege,

我遇到了同样的问题，但使用授予权限解决了，

when i run standalone application using the following query their is no error:

当我使用以下查询运行独立应用程序时，它们没有错误：

String sql = "select * from SCHEMA_NAME.TABLE_NAME";
Statement stmt = con.createStatement();
ResultSet rs = stmt.executeQuery(sql);

---

---

But when i run through a web-based application, As each user has different roles with permissions. If user doesn't have particular SELECT permission then it leads to the ERROR.

但是当我运行基于 Web 的应用程序时，由于每个用户具有不同的权限角色。如果用户没有特定的 SELECT 权限，则会导致错误。

For TABLES if the USER_ROLE does not have an select permission. Then leads to the following error as ORA-00942: Table or view does not exist:

对于 TABLES，如果 USER_ROLE 没有选择权限。然后导致以下错误ORA-00942: Table or view does not exist：

GRANT (Transact-SQL)- ADD SELECT privilege for tables:

GRANT (Transact-SQL)- 为表添加 SELECT 权限：

GRANT SELECT ON SCHEMA_NAME.TABLE_NAME TO "USER_ROLE";
GRANT SELECT, INSERT, UPDATE ON SCHEMA_NAME.TABLE_NAME TO "USER_ROLE";
GRANT SELECT, DELETE ON SCHEMA_NAME.TABLE_NAME TO "USER_ROLE";

Caused by: java.sql.SQLSyntaxErrorException: ORA-00942: Table or view does not exist
at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:445)
at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:396)
at oracle.jdbc.driver.T4C8Oall.processError(T4C8Oall.java:879)
at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:450)
at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:192)
at oracle.jdbc.driver.T4C8Oall.doOALL(T4C8Oall.java:531)
at oracle.jdbc.driver.T4CStatement.doOall8(T4CStatement.java:193)
at oracle.jdbc.driver.T4CStatement.executeForDescribe(T4CStatement.java:873)
at oracle.jdbc.driver.OracleStatement.executeMaybeDescribe(OracleStatement.java:1167)
at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1289)
at oracle.jdbc.driver.OracleStatement.executeQuery(OracleStatement.java:1491)
at oracle.jdbc.driver.OracleStatementWrapper.executeQuery(OracleStatementWrapper.java:406)

For Stored procedure if the USER_ROLE does not have an execute permission. Then leads to the following error as Internal Error: Inconsistent catalog view

对于存储过程，如果 USER_ROLE 没有执行权限。然后导致以下错误Internal Error: Inconsistent catalog view

ADD EXECUTE privilege for Stored Procedure/Functions:

为存储过程/函数添加EXECUTE 权限：

GRANT DEBUG ON SCHEMA_NAME.PROCEDURE_NAME TO "USER_ROLE";
GRANT EXECUTE ON SCHEMA_NAME.PROCEDURE_NAME TO "USER_ROLE";

java.sql.SQLException: Internal Error: Inconsistent catalog view
at oracle.sql.StructDescriptor.initMetaData1_9_0(StructDescriptor.java:1420)