Passing and using a List<string> as Oracle Binding Parameter-CodePudding

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[]

CodePudding user response：

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

CodePudding user response：

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)