I'm maintaining some old JDBC code, which is targeting an Oracle 10g database. I've come across a statement similar to:
SELECT param1, param2, param3 FROM mytable WHERE param1 = 'something' AND param2 = 'something-else' AND param3 IN ('*')
Does Oracle treat the asterisk as a wildcard? I can't find any examples online of that syntax, but maybe I'm not looking for the right thing. I'm assuming that it essentially turns off the IN clause, and leaving out the whole clause would have the same effect.
Ah, got it. It's looking for a literal asterisk in that field. I'm not sure why, but that didn't even occur to me.
Martin Vajsar wrote:
Edit: Oracle would be happier with binds, of course, but perhaps you didn't show them for clarity.
No, it's done as a concatenation of strings, and using ordinary Statements. Converting the queries to use PreparedStatements is part of my maintenance work to protect against SQL Injection. The code is fairly sloppy though. They often build up query strings on-the-fly, concatenating SQL fragments and parameters in deeply-nested if structures and method calls. They don't even always remember to close resources in a finally block. Also, it's so, so old that it stores the results into Vectors. Well, we go where to work is I suppose.