Oracle WHERE IN ('*')

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.

No, Oracle has the LIKE keyword and proper wildcard character (%) for that.

This is equivalent to:

SELECT param1, param2, param3 FROM mytable WHERE param1 = 'something' AND param2 = 'something-else' AND param3 = '*' ,

so removing the param3 condition changes the logic.

Edit: Oracle would be happier with binds, of course, but perhaps you didn't show them for clarity.

A bit of gueswork here, because I have no Oracle database available during weekends:

wildcard works with LIKE, not with IN.
To my knowledge, param3 IN ('*') is the same as param3 = '*'
It will only return rows where the param3 value is a single asterix.

My god i hope I'm not wrong.

Ah, Martin answered while I was typing.

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.

Greg Charles wrote:... Well, we go where to work is I suppose.

Yes. If it would all have been perfect, you 'd have to look for another job.