Page 1 of 1
Trouble with array parameter in OracleCommand
Posted: Tue 25 Oct 2005 04:11
by Sergius
I have an array of variable length as parameter and I call this command several times:
Code: Select all
//... I declare an array and pass it as parameter
string [] z = new string[3] { "a","b","c"};
OracleCommand1.Parameters[0].Value = z;
OracleCommand1.Parameters[0].ArrayLength = z.Length;
OracleCommand1.ExecuteNonQuery();
//It work fine!
//....
//Now try to increase array elements to 4:
z = new string[4] { "a","b","c", "d"};
OracleCommand1.Parameters[0].Value = z;
OracleCommand1.Parameters[0].ArrayLength = z.Length;
OracleCommand1.ExecuteNonQuery();
//It throw exception:
//....
System.IndexOutOfRangeException: Index was outside the bounds of the array.
at CoreLab.Oracle.OracleParameter.a(p& A_0, Boolean A_1, Boolean A_2, OracleCommand A_3, Byte[] A_4, x A_5, Boolean& A_6)
at CoreLab.Oracle.OracleCommand.a(Int32 A_0, OracleParameterCollection A_1, x A_2, Boolean& A_3)
at CoreLab.Oracle.OracleCommand.a(CommandBehavior A_0, IDisposable A_1, Int32 A_2, Int32 A_3)
at CoreLab.Common.DbCommandBase.b(CommandBehavior A_0)
at CoreLab.Oracle.OracleCommand.ExecuteReader()
How can I fix it?
Posted: Tue 25 Oct 2005 08:15
by Paul
We will fix this problem in the next OraDirect .NET build. Please use the following code now
Code: Select all
string [] z = new string[3] { "a","b","c"};
OracleCommand1.Parameters[0].ArrayLength = z.Length;
OracleCommand1.Parameters[0].Value = z;
OracleCommand1.ExecuteNonQuery();
//It work fine!
//....
//Now try to increase array elements to 4:
z = new string[4] { "a","b","c", "d"};
OracleCommand1.Parameters[0].ArrayLength = z.Length;
OracleCommand1.Parameters[0].Value = z;
OracleCommand1.ExecuteNonQuery();
Posted: Mon 14 Nov 2005 05:42
by Guest
In version 3.0.1.5 this code not work:
OracleCommand1.CommandType = CommandType.StoredProcedure;
OracleCommand1.Parameters.Add("result", OracleDbType.Cursor);
OracleCommand1.Parameters.Add("sss", OracleDbType.VarChar);
OracleCommand1.Parameters[0].Direction = ParameterDirection.ReturnValue;
OracleCommand1.Parameters[1].Direction = ParameterDirection.Input;
string [] z = new string[3] { "a","b","c"};
OracleCommand1.Parameters[0].ArrayLength = z.Length;
OracleCommand1.Parameters[0].Value = z;
OracleCommand1.ExecuteNonQuery();
Array is passed to Oracle with empty values, but in prev version(3.0.1.4) it work correctly.
Posted: Mon 14 Nov 2005 10:28
by Paul
We fixed this problem. This fix will be included in the OraDirect .NET build.
Posted: Thu 08 Dec 2005 07:34
by Sergius
OracleCommand1.CommandType = CommandType.StoredProcedure;
OracleCommand1.Parameters.Add("result", OracleDbType.Cursor);
OracleCommand1.Parameters.Add("sss", OracleDbType.VarChar);
OracleCommand1.Parameters[0].Direction = ParameterDirection.ReturnValue;
OracleCommand1.Parameters[1].Direction = ParameterDirection.Input;
string [] z = new string[3] { "a","b","c"};
OracleCommand1.Parameters[0].ArrayLength = z.Length;
OracleCommand1.Parameters[0].Value = z;
OracleCommand1.ExecuteNonQuery();
This code don't work with Oracle 8.1.7
It return Oracle error ORA-02005:
ORA-02005 implicit (-1) length not valid for this bind or define datatype
Cause: A negative length for the define variable was passed to a define function.
Action: An explicit, non-negative, length parameter must be passed.
Posted: Wed 21 Dec 2005 11:31
by schueler
Any good news on this thread? Any known other workaround? I'm waiting for this fix, since 4 weeks!
Posted: Wed 21 Dec 2005 14:03
by Paul
What is the declaration of your function? According to your code it returns the array of Cursors. Or do you want to pass string values to the second (Parameters[1]) parameter?
Posted: Mon 26 Dec 2005 01:32
by Sergius
Sorry, it was misprint
. The right code is
OracleCommand1.CommandType = CommandType.StoredProcedure;
OracleCommand1.Parameters.Add("result", OracleDbType.Cursor);
OracleCommand1.Parameters.Add("sss", OracleDbType.VarChar);
OracleCommand1.Parameters[0].Direction = ParameterDirection.ReturnValue;
OracleCommand1.Parameters[1].Direction = ParameterDirection.Input;
string [] z = new string[3] { "a","b","c"};
OracleCommand1.Parameters[1].ArrayLength = z.Length;
OracleCommand1.Parameters[1].Value = z;
OracleCommand1.ExecuteNonQuery();
But it doesn't matter - this code not work with Oracle 8.1.7
I want pass an array of string as parameter and get a cursor.
Posted: Mon 26 Dec 2005 09:31
by Paul
We reproduced your problem with OraDirect .NET 3.01.5 and fixed it. This fix will be included in the next OraDirect .NET build.
Posted: Tue 27 Dec 2005 12:26
by Guest
Sehr Gut!
