Win a copy of The Little Book of Impediments (e-book only) this week in the Agile and Other Processes forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

Oracle IN operator with PreparedStatement

 
Sonny Gill
Ranch Hand
Posts: 1211
IntelliJ IDE Mac
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hello,

How can I go about using a PreparedStatement for a SQL query that uses the Oracle IN operator.
I have something like
SELECT name FROM emp WHERE id IN ('A11', 'B12', 'C13')
The number of values specified in the IN-list is only known at run time.

Is there any way of using a PreparedStatement other than calculate the number of values at run-time and create a String with that many '?'s, and set them individually, in which case I might as well use a Statement, since the values to be put into IN-list are retrieved directly from the database, and will be genuine String values.

Cheers
Sonny
 
satish sathineni
Ranch Hand
Posts: 46
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
for IN operator u have to use statement only becoz as it doesnt make any difference as every time it has to compile the statement with the changing values...

For using IN operator the best idea would be Statement rather than Prepared Statement...

I also faced the problem as urs but i opted for Statement rather than Prepared Statement for reasons above mentioned....

cheers...
satish....
 
Avi Abrami
Ranch Hand
Posts: 1141
1
Java Oracle
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Sonny,

since the values to be put into IN-list are retrieved directly
from the database

Then you may probably be able to rewrite your query so that it doesn't need to use a variable-length "IN" list.

In any case, yours is an ultra FAQ. As far as I know, the only way is the one you have described. If you haven't already done so, I suggest going to the Ask Tom Web site and doing a search for the terms "variable in list".

Good Luck,
Avi.
 
Jeanne Boyarsky
author & internet detective
Marshal
Posts: 35279
384
Eclipse IDE Java VI Editor
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Sonny,
I've used a "batching" solution to use prepared statements with an in clause. I pick some predefined batch sizes (like 1, 4, 11 and 51.) I then fill up the largest batch size and submit it. Repeat for the remaining data.

The idea is that the database can truly prepare my prepared statement (since there are only four of them.)
 
Sonny Gill
Ranch Hand
Posts: 1211
IntelliJ IDE Mac
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Avi, I think I will rewrite it to use a subquery as the condition.
And thanks for the link, I did not know of that site, it seems really useful.

Jeanne, How exactly do you use the 'batching' solution? If you have a PreparedStatement with say 10 parameters, and at runtime you have 14 values that you need to use, do you execute it once with the first 10 values, and on the second pass, you set the 10 parameters by repeating the 4 values that are left?
or Have I got it all wrong?

Thanks guys

Cheers
 
Jeanne Boyarsky
author & internet detective
Marshal
Posts: 35279
384
Eclipse IDE Java VI Editor
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Sonny,
Suppose your batch sizes are 1, 4 and 10. For 14, you would do one query with ten parameters. Then, you would do a query with four parameters. This would be a separate SQL string with only four parameters. You wouldn't use the full one with 10 parameters, because those would be wasted. The idea is to have a finite number of distinct sql queries in your prepared statements so they stay in the cache.
 
Sonny Gill
Ranch Hand
Posts: 1211
IntelliJ IDE Mac
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Thanks Jeanne.

Incidently, I happened to come accross this article that goes into some performance issues with using IN-lists with Oracle 7, in case anybody finds that useful.
Tuning Oracle for IN-lists
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic