Passing and using a List<string> 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
– MT0, Commented Sep 9, 2022 at 18:10

ibre5041 · Accepted Answer · 2022-09-12 14:08:40Z

0

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

answered Sep 12, 2022 at 14:08

ibre5041

5,3261 gold badge24 silver badges40 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Rico Strydom · Accepted Answer · 2022-09-12 16:15:37Z

0

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)

answered Sep 12, 2022 at 16:15

Rico Strydom

6111 gold badge9 silver badges29 bronze badges

Collectives™ on Stack Overflow

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

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related