Trouble with array parameter in OracleCommand

Trouble with array parameter in OracleCommand

Postby Sergius » Tue 25 Oct 2005 04:11

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?
Sergius
 

Postby Paul » Tue 25 Oct 2005 08:15

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();
Paul
 
Posts: 725
Joined: Thu 28 Oct 2004 14:06

Postby Guest » Mon 14 Nov 2005 05:42

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.
Guest
 

Postby Paul » Mon 14 Nov 2005 10:28

We fixed this problem. This fix will be included in the OraDirect .NET build.
Paul
 
Posts: 725
Joined: Thu 28 Oct 2004 14:06

Postby Sergius » Thu 08 Dec 2005 07:34

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.
Sergius
 

Postby schueler » Wed 21 Dec 2005 11:31

Any good news on this thread? Any known other workaround? I'm waiting for this fix, since 4 weeks!
schueler
 
Posts: 17
Joined: Tue 17 May 2005 15:44
Location: Germany

Postby Paul » Wed 21 Dec 2005 14:03

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?
Paul
 
Posts: 725
Joined: Thu 28 Oct 2004 14:06

Postby Sergius » Mon 26 Dec 2005 01:32

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.
Sergius
 

Postby Paul » Mon 26 Dec 2005 09:31

We reproduced your problem with OraDirect .NET 3.01.5 and fixed it. This fix will be included in the next OraDirect .NET build.
Paul
 
Posts: 725
Joined: Thu 28 Oct 2004 14:06

Postby Guest » Tue 27 Dec 2005 12:26

Sehr Gut! :)
Guest
 


Return to dotConnect for Oracle