Insert list of values in where clause from c# using oracle db

I've been searching for a while now 开发者_如何学JAVAfor a way to execute a select statement on an oracle database which contains an arbitray number of elements in the where clause. In principle what I want to do is something like this:

select * from my_table
where my_col in (:inputListHere)

Looking around I found the following solutions, none of which seem to properly work:

1. Using dynamic sql and just insert the list as a string of comma seperated elements.

   Works only for short lists. Long lists break the 4000 char limit for command string literals. (Btw.: I don't need to worry about sql injection attacks, so in principle dynamic sql would be an option.)

2. Using associative arrays in a stored proc. From what I could find the only way to use associative arrays in a stored proc is to iterate over them but that would give terrible performance when I want to get all the differenct occurence of elements in a list in a table.

3. Using bind arrays with a stored proc. Same issue here: Since the stored proc is called once for every element in the bind array the performance is terrible.

4. Inserting the list that I want to use in the where clause into the db beforehand and then executing a query which has something like select list_items from tmp_list_store in place of :inputListHere. The performance here turned out to be better than for option 2. and 3. but worse than for option 1 and an additional disadvantage is that the user that executes the query needs to have write access to this one table on the database even though he just wants to read from the db.

If you have any better options or ideas how to improve upon one of these options it would be much appreciated.

Send list of values as xml string.

Sample of how to process xml by using XMLTABLE:

 SELECT cid1 FROM SomeTable 
    WHERE cid2 in ( 
        SELECT val 
            FROM XMLTABLE('/list/val' 
                PASSING xmltype('<list><val>a1</val><val>a2</val><val>a3</val></list>')
                COLUMNS val VARCHAR2(32) PATH '/'));
    );

Or:

1. Create new oracle type TABLE OF NUMBER / or VARCHAR
2. Create new oracle function that will parse your list to table of number or varchar

3. Then you can do something like this:

   SELECT cid1 FROM SomeTable WHERE cid2 in (SELECT * FROM table(LIST_TO_NUMBER_TABLE(p_YourValuesList)));

From .NET simply pass parameter value in this format: 12,33,54,35,65

Here is sample if list_to_table function:

create or replace FUNCTION LIST_TO_NUMBER_TABLE
(
    p_LIST IN VARCHAR2
)
RETURN NUMBER_TABLE PIPELINED
AS

    STRING_       LONG := p_LIST || ',';
    COMMAINDEX_   PLS_INTEGER;
    INDEX_        PLS_INTEGER := 1;

BEGIN
    LOOP
        COMMAINDEX_ := INSTR(STRING_, ',', INDEX_);
        EXIT WHEN COMMAINDEX_ = 0;
        PIPE ROW (TO_NUMBER(SUBSTR(STRING_, INDEX_, COMMAINDEX_ - INDEX_)));
        INDEX_ := COMMAINDEX_ + 1;
    END LOOP;
    RETURN;
END;

After gaining a bit more Database knowledge in the past half year I have come to the following conclusion: As far as a solution to the problem I proposed goes the answer that HABJAN gave in addition to the options I proposed are probably the most reasonable solutions.
The reason why none of those solutions are quite working out is because the correct answer, I think, is that if you have a couple of thousand items to transfer you shouldn't have them as parameters but as values in a separate table in the database. Otherwise the design should probably be rethought. In that sense option 4 is probably closest to the correct answer but not having the problem at all because you improve your design is probably the best answer.