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:

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

The query is being run through a JDBC connection.

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.

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

回答1:

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.

回答2:

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.

回答3:

I had a similar problem using Oracle 10g and java, the use of PesonID in the following line was causing the error.

 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.