oracle 间歇性 ORA-00904: : 无效标识符
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/4885108/
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
Intermittent ORA-00904: : invalid identifier
提问by Ellis
Does anyone know what could be causing a fixed query (static final String) to work most of the time and then intermittently throw the following error:
有谁知道什么可能导致固定查询（静态最终字符串）在大部分时间工作，然后间歇性地抛出以下错误：
Inner cause: java.sql.SQLException: ORA-00904: : invalid identifier
内在原因：java.sql.SQLException：ORA-00904：：标识符无效
The query is being run through a JDBC connection.
查询正在通过 JDBC 连接运行。
The curious thing here is that the identifier is empty, and of course that the query works intermittently. If I take the sql that has been logged out and run it through plsql developer it all works fine.
这里奇怪的是标识符是空的，当然查询会间歇性地工作。如果我使用已注销的 sql 并通过 plsql developer 运行它，则一切正常。
Any ideas?
有任何想法吗？
Here's the query. It has been obfuscated for security reasons.
这是查询。出于安全原因，它已被混淆。
SELECT b.field1,
b.field2,
b.field3,
my_func(b.field4, ?, nvl2(b.field5, 1, 0)) cardnumber,
b.field6,
b.field7,
b.field8,
b.field9,
b.field10,
b.field11,
b.field12,
b.field13,
b.field14,
b.field15,
b.field16,
b.field17,
b.field18,
b.field19,
b.field20,
b.field21,
b.field22,
b.field23,
b.field24,
b.field25,
b.field26,
my_func(b.field27, ?, nvl2(b.field28, 1, 0)) account_number,
b.field29,
s.field30 source_name,
b.field31
from table1 b
left join table2 s
on b.source_id = s.source_id
where b.fieldx in
(select fieldx from tablex where fieldy = ?)
and customer_id = ?
and state not in (7, 12, 1, 3, 13)
UNION
SELECT b.field1,
b.field2,
b.field3,
my_func(b.field4, ?, nvl2(b.field5, 1, 0)) cardnumber,
b.field6,
b.field7,
b.field8,
b.field9,
b.field10,
b.field11,
b.field12,
b.field13,
b.field14,
b.field15,
b.field16,
b.field17,
b.field18,
b.field19,
b.field20,
b.field21,
b.field22,
b.field23,
b.field24,
b.field25,
b.field26,
my_func(b.field27, ?, nvl2(b.field28, 1, 0)) account_number,
b.field29,
s.field30 source_name,
b.field31
from table1 b
left join table2 s
on b.source_id = s.source_id
where b.field3 in
(select fieldx from table7 where fieldy = ?)
and customer_id = ?
and state in (1, 3)
AND (b.field1 not in
(select b.fieldx
from table1 b,
table3 sb,
table4 sba
where b.source_id = sb.source_id
and sb.attribute_id = sba.attribute_id
and sba.name = 'HIDE_IN_MENU'
and b.customer_id = ?))
回答by Gary Myers
How is the statement executed ? If there is some form of concatenation rather than binding of variables that may cause an issue. Perhaps bind values aren't being defined or there are some junk values in there.
语句是如何执行的？如果存在某种形式的串联而不是可能导致问题的变量绑定。也许绑定值没有被定义或者那里有一些垃圾值。
Could be the error is coming from the execution of MY_FUNC rather than the calling statement.
可能是错误来自 MY_FUNC 的执行而不是调用语句。
回答by Richipal
I had a similar problem using Oracle 10g and java, the use of PesonID in the following line was causing the error.
我在使用 Oracle 10g 和 java 时遇到了类似的问题，在下一行中使用 PesonID 导致了错误。
String sql= "SELECT * FROM Person where PersonID=?"
but when I used the following it worked fine.
但是当我使用以下内容时，它工作正常。
String sql = "SELECT * FROM Person where \"PersonID\"=?"
so the key is those extra quotes.
所以关键是那些额外的引号。
回答by Lev Khomich
Looks like kind of a bugs 5355253, 5458021, 5717746 etc. Try to flush shared pool, this helps in most cases. If it doesn't, you could provide additional info like DBMS version and platform.
看起来有点像错误 5355253、5458021、5717746 等。尝试刷新共享池，这在大多数情况下会有所帮助。如果没有，您可以提供其他信息，例如 DBMS 版本和平台。
