oracle 为什么即使列存在，我也有 ORA-00904？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/5722851/
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
Why do I have ORA-00904 even when the column is present?
提问by Victor
I see an error while executing hibernate sql query.
我在执行 hibernate sql 查询时看到错误。
java.sql.SQLException: ORA-00904: "table_name"."column_name": invalid identifier
java.sql.SQLException: ORA-00904: "table_name"."column_name": 无效标识符
When I open up the table in sqldeveloper, the column is present.
当我在 sqldeveloper 中打开表时，该列存在。
The error is only happening in PROD, not in DEV.
该错误仅发生在 PROD 中，而不发生在 DEV 中。
What should I check?
我应该检查什么？
采纳答案by Jon Heller
It could be a case-sensitivity issue. Normally tables and columns are not case sensitive, but they will be if you use quotation marks. For example:
这可能是一个区分大小写的问题。通常表和列不区分大小写，但如果您使用引号，它们就会区分大小写。例如：
create table bad_design("goodLuckSelectingThisColumn" number);
回答by vls
Oracle will throw ORA-00904if executing user does not have proper permissions on objects involved in the query.
ORA-00904如果执行用户对查询中涉及的对象没有适当的权限，Oracle 将抛出异常。
回答by johanwannheden
This happened to me when I accidentally defined two entities with the same persistent database table. In one of the tables the column in question did exist, in the other not. When attempting to persist an object (of the type referring to the wrong underlying database table), this error occurred.
当我不小心用相同的持久数据库表定义了两个实体时，这发生在我身上。在其中一个表中，有问题的列确实存在，而在另一个表中不存在。当试图持久化一个对象（类型指的是错误的底层数据库表）时，发生了这个错误。
回答by Arc
Write the column name in between DOUBLE quote as in "columnName".
在双引号之间写入列名，如“columnName”。
If the error message shows a different character case than what you wrote, it is very likely that your sql client performed an automatic case conversion for you. Use double quote to bypass that. (This works on Squirrell Client 3.0).
如果错误消息显示的字符大小写与您所写的不同，则很可能是您的 sql 客户端为您执行了自动大小写转换。使用双引号绕过它。（这适用于 Squirrell Client 3.0）。
回答by Piyush Ugale
It is because one of the DBs the column was created with " which makes its name case-sensitive.
这是因为列是用 " 创建的数据库之一，这使得它的名称区分大小写。
Oracle Table Column Name : GoodRec Hive cannot recognize case sensitivity : ERROR thrown was - Caused by: java.sql.SQLSyntaxErrorException: ORA-00904: "GOODREC": invalid identifier
Oracle 表列名称：GoodRec Hive 无法识别区分大小写：抛出的错误是 - 引起：java.sql.SQLSyntaxErrorException：ORA-00904：“GOODREC”：标识符无效
Solution : Rename Oracle column name to all caps.
解决方案：将 Oracle 列名重命名为全部大写。
回答by yogi
check the position of Column annotation in java class for the field For Example,consider one table with name STUDENT with 3 column(Name,Roll_No,Marks).
检查字段的java类中Column注释的位置例如，考虑一个名为STUDENT的表，其中包含3列（Name，Roll_No，Marks）。
Then make sure you have added below annotation of column before Getter Methodinstead of Setter method. It will solve ur problem @Column(name = "Name", length = 100)
然后确保您在 Getter 方法而不是 Setter 方法之前添加了列的注释。它将解决你的问题 @Column(name = "Name", length = 100)
**@Column(name = "NAME", length = 100)
public String getName() {**
return name;
}
public void setName(String name) {
this.name= name;
}
回答by Rajesh Chamarthi
Have you compared the table definitions in Prod and Dev?
您是否比较了 Prod 和 Dev 中的表定义？
And when you are running it in SQL Developer, are you running the query in Prod (same database as the application) and with the same user?
当您在 SQL Developer 中运行它时，您是否在 Prod（与应用程序相同的数据库）中使用相同的用户运行查询？
If there are some additional columns that you are adding (using an alter command) and these changes are not yet promoted to prod, this issue is possible.
如果您正在添加一些额外的列（使用 alter 命令）并且这些更改尚未升级到 prod，则可能会出现此问题。
Can you post the definition of the table and your actual Query?
你能发布表的定义和你的实际查询吗？
回答by Robert Wiley II
I use Toad for Oracle and if the table is owned by another username than the one you logged in as and you have access to read the table, you still may need to add the original table owner to the table name.
我使用 Toad for Oracle，如果该表的所有者不是您登录的用户名，并且您有权读取该表，则您可能仍需要将原始表所有者添加到表名中。
For example, lets say the table owner's name is 'OWNER1' and you are logged in as 'USER1'. This query may give you a ORA-00904 error:
例如，假设表所有者的名称是“OWNER1”，而您以“USER1”身份登录。这个查询可能会给你一个 ORA-00904 错误：
select * from table_name where x='test';
Prefixing the table_name with the table owner eliminated the error and gives results:
使用表所有者前缀 table_name 消除了错误并给出了结果：
select * from
回答by uncaught_exceptions
Check the username credential used to login to the database. (persistence.xml ??). The problem mostly is, the username\password used to login to the database, does not have visiblity to the object (table_name in this case). ( try logging in to sql developer, using the same username\password available in your data source)
检查用于登录数据库的用户名凭据。(persistence.xml ??)。问题主要是，用于登录数据库的用户名\密码对对象（在本例中为 table_name）不可见。（尝试登录 sql developer，使用数据源中可用的相同用户名\密码）
