Win a copy of Programmer's Guide to Java SE 8 Oracle Certified Associate (OCA) this week in the OCAJP forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

Oracle WHERE IN ('*')

 
Greg Charles
Sheriff
Posts: 2989
12
Firefox Browser IntelliJ IDE Java Mac Ruby
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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.
 
Martin Vajsar
Sheriff
Posts: 3752
62
Chrome Netbeans IDE Oracle
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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.
 
Jan Cumps
Bartender
Posts: 2599
12
C++ Linux Netbeans IDE
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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.
 
Greg Charles
Sheriff
Posts: 2989
12
Firefox Browser IntelliJ IDE Java Mac Ruby
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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.
 
Jan Cumps
Bartender
Posts: 2599
12
C++ Linux Netbeans IDE
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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.
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic