Java 出现异常 ORA-00942: 表或视图不存在 - 插入现有表时
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/6561650/
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
Getting an exception ORA-00942: table or view does not exist - when inserting into an existing table
提问by aquero
I am getting below exception, when trying to insert a batch of rows to an existing table
我在尝试将一批行插入现有表时遇到异常
ORA-00942: table or view does not exist
ORA-00942: 表或视图不存在
I can confirm that the table exists in db and I can insert data to that table using oracle sql developer. But when I try to insert rows using preparedstatement in java, its throwing table does not exist error.
我可以确认该表存在于 db 中，并且可以使用 oracle sql developer 将数据插入到该表中。但是当我尝试在java中使用preparedstatement插入行时，它的抛出表不存在错误。
Please find the stack trace of error below
请在下面找到错误的堆栈跟踪
java.sql.SQLException: ORA-00942: table or view does not exist
at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:134)
at oracle.jdbc.ttc7.TTIoer.processError(TTIoer.java:289)
at oracle.jdbc.ttc7.Oall7.receive(Oall7.java:573)
at oracle.jdbc.ttc7.TTC7Protocol.doOall7(TTC7Protocol.java:1889)
at oracle.jdbc.ttc7.TTC7Protocol.parseExecuteFetch(TTC7Protocol.java:1093)
at oracle.jdbc.driver.OracleStatement.executeNonQuery(OracleStatement.java:2047)
at oracle.jdbc.driver.OracleStatement.doExecuteOther(OracleStatement.java:1940)
at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout>>(OracleStatement.java:2709)
at oracle.jdbc.driver.OraclePreparedStatement.executeUpdate(OraclePreparedStatement.java:589)
at quotecopy.DbConnection.insertIntoDestinationDb(DbConnection.java:591)
at quotecopy.QuoteCopier.main(QuoteCopier.java:72)
Can anyone suggest the reasons for this error ?
任何人都可以提出这个错误的原因吗？
Update :Issue solved
更新：问题已解决
There was no problem with my database connection properties or with my table or view name. The solution to the problem was very strange. One of the columns that I was trying insert was of Clob type. As I had a lot of trouble handling clob data in oracle db before, gave a try by replacing the clob setter with a temporary string setter and the same code executed with out any problems and all the rows were correctly inserted!!!.
我的数据库连接属性或我的表或视图名称没有问题。问题的解决方法很奇怪。我尝试插入的列之一是 Clob 类型。由于我之前在 oracle db 中处理 clob 数据时遇到了很多麻烦，因此尝试用临时字符串 setter 替换 clob setter 并执行相同的代码，没有任何问题，并且所有行都已正确插入！！！。
ie. peparedstatement.setClob(columnIndex, clob)
IE。peparedstatement.setClob（列索引，clob）
was replaced with
被替换为
peparedstatement.setString(columnIndex, "String")
peparedstatement.setString(columnIndex, "String")
Why an error table or view does exist error was throws for error in inserting clob data. Could anyone of you please explain ?
为什么错误表或视图确实存在错误是因为插入 clob 数据时出错。你们中的任何人都可以解释一下吗？
Thanks a lot for your answers and comments.
非常感谢您的回答和评论。
采纳答案by aquero
There was no problem with my database connection properties or with my table or view name. The solution to the problem was very strange. One of the columns that I was trying insert was of Clob type. As I had a lot of trouble handling clob data in oracle db before, gave a try by replacing the clob setter with a temporary string setter and the same code executed with out any problems and all the rows were correctly inserted!!!.
我的数据库连接属性或我的表或视图名称没有问题。问题的解决方法很奇怪。我尝试插入的列之一是 Clob 类型。由于我之前在 oracle db 中处理 clob 数据时遇到了很多麻烦，因此尝试用临时字符串 setter 替换 clob setter 并执行相同的代码，没有任何问题，并且所有行都已正确插入！！！。
ie. peparedstatement.setClob(columnIndex, clob)
IE。peparedstatement.setClob（列索引，clob）
was replaced with
被替换为
peparedstatement.setString(columnIndex, "String")
peparedstatement.setString(columnIndex, "String")
回答by rsp
Is your script providing the schema name, or do you rely on the user logged into the database to select the default schema?
您的脚本是提供架构名称，还是依赖登录到数据库的用户来选择默认架构？
It might be that you do not name the schema and that you perform your batch with a system user instead of the schema user resulting in the wrong execution context for a script that would work fine if executed by the user that has the target schema set as default schema. Your best action would be to include the schema name in the insert statements:
可能是您没有命名架构，并且您使用系统用户而不是架构用户执行批处理，从而导致脚本的执行上下文错误，如果由目标架构设置为默认架构。您最好的操作是在插入语句中包含架构名称：
INSERT INTO myschema.mytable (mycolums) VALUES ('myvalue')
update:Do you try to bind the table name as bound value in your prepared statement? That won't work.
更新：您是否尝试在准备好的语句中将表名绑定为绑定值？那行不通。
回答by unbeli
Oracle will also report this error if the table exists, but you don't have any privileges on it. So if you are sure that the table is there, check the grants.
如果该表存在，但您没有任何权限，Oracle 也会报告此错误。因此，如果您确定该表在那里，请检查赠款。
回答by Adrian
There seems to be some issue with setCLOB() that causes an ORA-00942 under some circumstances when the target table does exist and is correctly privileged. I'm having this exact issue now, I can make the ORA-00942 go away by simply not binding the CLOB into the same table.
setCLOB() 似乎存在一些问题，当目标表确实存在并且具有正确的特权时，在某些情况下会导致 ORA-00942。我现在遇到了这个确切的问题，我可以通过简单地不将 CLOB 绑定到同一个表来使 ORA-00942 消失。
I've tried setClob() with a java.sql.Clob and setCLOB() with an oracle.jdbc.CLOB but with the same result.
我试过 setClob() 和 java.sql.Clob 和 setCLOB() 和 oracle.jdbc.CLOB 但结果相同。
As you say, if you bind as a string the problem goes away - but this then limits your data size to 4k.
正如您所说，如果您绑定为字符串，问题就会消失 - 但这会将您的数据大小限制为 4k。
From testing it seems to be triggered when a transaction is open on the session prior to binding the CLOB. I'll feed back when I've solved this...checking Oracle support.
从测试来看，它似乎是在绑定 CLOB 之前在会话上打开事务时触发的。当我解决这个问题时，我会反馈......检查 Oracle 支持。
回答by Jonathan Dixon
@unbeli is right. Not having appropriate grants on a table will result in this error. For what it's worth, I recently experienced this. I was experiencing the exact problem that you described, I could execute insert statements through sql developer but would fail when using hibernate. I finally realized that my code was doing more than the obvious insert. Inserting into other tables that did not have appropriate grants. Adjusting grant privileges solved this for me.
@unbeli 是对的。表上没有适当的授权将导致此错误。对于它的价值，我最近经历了这一点。我遇到了您描述的确切问题，我可以通过 sql developer 执行插入语句，但在使用 hibernate 时会失败。我终于意识到我的代码所做的不仅仅是明显的插入。插入到其他没有适当授权的表中。调整授予权限为我解决了这个问题。
Note: Don't have reputation to comment, otherwise this may have been a comment.
注意：不要有评论的声誉，否则这可能是评论。
回答by Bren
I found how to solve this problem without using JDBC's setString() method which limits the data to 4K.
我找到了如何在不使用将数据限制为 4K 的 JDBC 的 setString() 方法的情况下解决此问题。
What you need to do is to use preparedStatement.setClob(int parameterIndex, Reader reader). At least this is what that worked for me. Thought Oracle drivers converts data to character stream to insert, seems like not. Or something specific causing an error.
你需要做的是使用preparedStatement.setClob(int parameterIndex, Reader reader)。至少这对我有用。以为 Oracle 驱动程序将数据转换为字符流以插入，似乎不是。或导致错误的特定内容。
Using a characterStream seems to work for me. I am reading tables from one db and writing to another one using jdbc. And i was getting table not found error just like it is mentioned above. So this is how i solved the problem:
使用 characterStream 似乎对我有用。我正在从一个数据库读取表并使用 jdbc 写入另一个数据库。就像上面提到的那样，我遇到了找不到表的错误。所以这就是我解决问题的方法：
case Types.CLOB: //Using a switch statement for all columns, this is for CLOB columns
Clob clobData = resultSet.getClob(columnIndex); // The source db
if (clobData != null) {
preparedStatement.setClob(columnIndex, clobData.getCharacterStream());
} else {
preparedStatement.setClob(columnIndex, clobData);
}
clobData = null;
return;
All good now.
现在一切都很好。
回答by diadyne
We experienced this issue on a BLOB column. Just in case anyone else lands on this question when encountering this error, here is how we resolved the issue:
我们在 BLOB 列上遇到了这个问题。以防万一其他人在遇到此错误时遇到此问题，以下是我们解决此问题的方法：
We started out with this:
我们从这个开始：
preparedStatement.setBlob(parameterIndex, resultSet.getBlob(columnName)); break;
We resolved the issue by changing that line to this:
我们通过将该行更改为以下内容解决了该问题：
java.sql.Blob blob = resultSet.getBlob(columnName);
if (blob != null) {
java.io.InputStream blobData = blob.getBinaryStream();
preparedStatement.setBinaryStream(parameterIndex, blobData);
} else {
preparedStatement.setBinaryStream(parameterIndex, null);
}
回答by user2607028
It works for me:
这个对我有用：
Clob clob1;
while (rs.next()) {
rs.setString(1, rs.getString("FIELD_1"));
clob1 = rs.getClob("CLOB1");
if (clob1 != null) {
sta.setClob(2, clob1.getCharacterStream());
} else {
sta.setClob(2, clob1);
}
clob1 = null;
sta.setString(3, rs.getString("FIELD_3"));
}
回答by user8951209
Here I got the solution for the question. The problem is on glass fish if you are using it. When you create JNDI name make sure pool name is correct and pool name is the name of connection pool name that you are created.
在这里，我得到了问题的解决方案。如果您使用的是玻璃鱼，则问题出在它身上。创建 JNDI 名称时，请确保池名称正确，池名称是您创建的连接池名称。
