wood burning stoves 2.0*
The moose likes JDBC and the fly likes Oracle WHERE IN ('*') Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of OCA/OCP Java SE 7 Programmer I & II Study Guide this week in the OCPJP forum!
JavaRanch » Java Forums » Databases » JDBC
Bookmark "Oracle WHERE IN ( Watch "Oracle WHERE IN ( New topic
Author

Oracle WHERE IN ('*')

Greg Charles
Sheriff

Joined: Oct 01, 2001
Posts: 2854
    
  11

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

Joined: Aug 22, 2010
Posts: 3610
    
  60

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

Joined: Dec 20, 2006
Posts: 2504
    
    8

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.


OCUP UML fundamental and ITIL foundation
youtube channel
Greg Charles
Sheriff

Joined: Oct 01, 2001
Posts: 2854
    
  11

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

Joined: Dec 20, 2006
Posts: 2504
    
    8

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.
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: Oracle WHERE IN ('*')