Parameters as array

Parameters as array

Postby AndyR » Wed 07 Aug 2013 12:02

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
AndyR
 
Posts: 3
Joined: Mon 10 Sep 2012 07:32

Re: Parameters as array

Postby Pinturiccio » Thu 08 Aug 2013 15:01

We have reproduced the issue. We will investigate it and notify you about the results as soon as possible.
Pinturiccio
Devart Team
 
Posts: 1866
Joined: Wed 02 Nov 2011 09:44

Re: Parameters as array

Postby Pinturiccio » Mon 12 Aug 2013 14:42

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;
Pinturiccio
Devart Team
 
Posts: 1866
Joined: Wed 02 Nov 2011 09:44


Return to dotConnect for Oracle