间歇性ORA-00904::无效的标识符 [英] Intermittent ORA-00904: : invalid identifier

问题描述

有人知道什么可能导致固定查询(静态最终字符串)在大多数情况下正常工作，然后间歇地引发以下错误:

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:

内部原因:java.sql.SQLException:ORA-00904 ::无效的标识符

Inner cause: java.sql.SQLException: ORA-00904: : invalid identifier

查询正在通过JDBC连接运行.

The query is being run through a JDBC connection.

这里令人奇怪的是，标识符为空，当然查询是间歇性地工作的.如果我使用已注销的sql并通过plsql developer运行它，则一切正常.

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.

有什么想法吗?

这是查询.由于安全原因，它已被混淆.

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 = ?))

推荐答案

该语句如何执行? 如果存在某种形式的串联而不是变量绑定，则可能导致问题.也许未定义绑定值，或者其中存在一些垃圾值.

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.

错误可能是由于执行MY_FUNC而不是调用语句引起的.

Could be the error is coming from the execution of MY_FUNC rather than the calling statement.

这篇关于间歇性ORA-00904::无效的标识符的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！