Bind variables in a WHERE IN () clause

Hi all

I wonder if someone has a solution to the following problem:

A huge SQL query is fired by an application (via JDBC - so the concept 
of "bind variables" generally works - using a PreparedStatement type).

However, one of the variables would be a list of IDs for use in:

 select all_this_stuff 
 ... where ID in (:list_of_ids)

I have been forced to create a "new" SQL statement each time, as I can't 
just pass "1,2,3" as a variable value - that would just be understood as 
the string "1,2,3" and not make any sense.

In Mr. Kyte's book "Expert one on one - Oracle" (p. 893) I was happy to 
find a way of creating a PL/SQL function - str2tbl - to convert a text 
string to a self-defined TABLE of NUMBER type which can be used from SQL 
as:

 select all_this_stuff 
 ... where ID in ( str2tbl( :string_2_be_converted ) )

- allowing the bind variable to be a text string.

Success - and catastrophy...
It does work. But performance is a disaster!
It turns out that Oracle (8.17) expects the (few) values, returned from 
the function to be a huge result set, and therefore insists on doing 
EVERYTHING else first.
That first of all means that the index on the IDs is not used, and a full 
table scan is done - before it is established which rows are needed via 
the function.

Of course, one could always insert the IDs in a physical table, prior to 
the query, and delete them afterwards. But that would mean 3 SQL 
statements each time - whereas the "function" solution would keep it at 
one. If it did not ruin the "PLAN", that is.

Anybody had any experience with this type of problem? Or any ideas?

Thanks
Jens, Denmark
---
Change your mail options at http://p2p.wrox.com/manager.asp or 
to unsubscribe send a blank email to [email protected].