Passing and using a List as Oracle Binding Parameter

Question

I have a query ending with :

AND U2.USER_ID IN (:ToUserIDs)

My ToUserIDs is List<string> being created from entries in a TextBox.:

ToUserIDs = new List<string>(ToUserIDsTextBox.Text.Split(new string[] { "\r\n" }, StringSplitOptions.RemoveEmptyEntries));

My OracleParameter is set up as follow:

OracleParameter oracleParameter3 = oracleDataAdapter.SelectCommand.Parameters.Add(":ToUserIDs", OracleDbType.NVarchar2);
oracleParameter3.Direction = ParameterDirection.Input;
oracleParameter3.CollectionType = OracleCollectionType.PLSQLAssociativeArray;
oracleParameter3.Value = (List<string>)args["ToUserIDs"];

Here I am running into deep water as I am not sure who to handle Lists/Arrays in an OracleParameter.

I am getting this error:

Unable to cast object of type 'System.Collections.Generic.List`1[System.String]' to type 'System.String[]

An associative array is a PL/SQL data type and cannot be used in an SQL context so even if you fix the C# errors casting between data types, I'm not sure you can then fix the underlying issue with passing a list of strings. One method may be to pass it to a procedure (or function) which converts the associative array to a non-associative collection and then uses that in a query. — MT0, Sep 09 '22 at 18:10

score 0 · Answer 1 · answered Sep 12 '22 at 14:08

This wont work. The IN in clause only supports:

fixed length of scalars, like:
```
AND U2.USER_ID IN (:P1, :P2, :P3)
```
a subquery
```
AND U2.USER_ID IN (select x from y)
```

You can rewrite your query using TABLE cast keyword

   AND U2.USER_ID IN (select * from TABLE(:P1))

Where P1 is parameter of type: array of varhar2

score 0 · Answer 2 · answered Sep 12 '22 at 16:15

Used a post by @saminpa (Oracle Parameters with IN statement?) as solution.

AND         U2.USER_ID      IN (SELECT IDS.ToUserID FROM XMLTABLE('/ToUserIDs/ToUserID' PASSING XMLTYPE (:ToUserIDsElement) COLUMNS ToUserID VARCHAR(100) PATH '/ToUserID/.') IDS)

Passing and using a List as Oracle Binding Parameter

2 Answers2