Intermittent ORA-00904: : invalid identifier

Question

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

Answer 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.