Parameters as array

[AndyR]

Hi,

i am currently trying to use a OracleParameter as array for following type of select

select * from mytable where mytable.column in (:PARAMETER1)

I am using following code

Code: Select all

private static OracleCommand BuildCommand(string istatement, Dictionary<string, object> ispalten)
        {
            var comm = new OracleCommand {Connection = UserConnection.getConnection()};
            OracleParameterCollection xparameters = comm.Parameters;
            xparameters.Clear();
            if (ispalten != null)
            {
                foreach (var keyValuePair in ispalten)
                {
                    var param = new OracleParameter();
                    string inhalt = "123,3333,2222";
                    param.ArrayLength = inhalt.Split(',').Length;
                    var dummy = new decimal[param.ArrayLength];
                    for (int i = 0; i < inhalt.Split(',').Length; i++)
                     {
                       dummy[i] = Convert.ToDecimal(inhalt.Split(',')[i]);
                     }
                     param.Value = dummy;
                  }
                param.ParameterName = keyValuePair.Key.ToUpper();                
                param.Direction = ParameterDirection.Input;
                xparameters.Add(param);
                }
            }
            comm.CommandText = istatement;
            return comm;
        }

No after the code is eecuted, i get a ORA_Exception:
ORA-01484: arrays can only be bound to PL/SQL statements.

So my question is, how can i use an array a Parameter?

Thanks a lot andy

[Pinturiccio]

We have reproduced the issue. We will investigate it and notify you about the results as soon as possible.

[Pinturiccio]

The ArrayLength property of OracleParameter is used for Array Binding, and it cannot be used as in your example.

An array can be passed as a parameter only to a PL/SQL block or a procedure, where the array must be processed accordingly. In your case it is better to implement the IN operator in the following way:

Code: Select all

IN (:par1, :par2, ... , :parN) //N = inhalt.Split(',').Length;