Forums Register Login

Oracle IN operator with PreparedStatement

+Pie Number of slices to send: Send
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
+Pie Number of slices to send: Send
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....
+Pie Number of slices to send: Send
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.
+Pie Number of slices to send: Send
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.)
+Pie Number of slices to send: Send
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
+Pie Number of slices to send: Send
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.
+Pie Number of slices to send: Send
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
Think of how dumb the average person is. Mathematically, half of them are EVEN DUMBER. Smart tiny ad:
a bit of art, as a gift, that will fit in a stocking
https://gardener-gift.com


reply
reply
This thread has been viewed 7168 times.
Similar Threads
Batching Select Statements in JDBC
java.sql.SQLException: ORA-00903: invalid table name
Error Numeric Overflow-oracle
PreparedStatement's - how do you use them CORRECTLY?
How prepared statements shall be used
More...

All times above are in ranch (not your local) time.
The current ranch time is
Mar 28, 2024 14:09:07.