Retrieve the Value of PLSQLAssociativeArray in C#

Actually my code pass various type of List array as a parameter to Oracle by using Oracle Stored Procedure. The Code Given

List AccNo = new List();
List TrnAmt = new List();
List TranNo = new List();
List rec = new List();
AccNo.Add(data_read3[3].ToString());
TrnAmt.Add( Double.Parse(data_read3[0].ToString()));
TranNo.Add(trnno); rec = cn_new.Call_Procedure_Host("Host_to_Host.host_record", AccNo, TrnAmt, TranNo, CONNECTION_STRING_other);

Now Call_Procedure_Host will return out parameter which is also a array list.The code given

public List Call_Procedure_Host(string a, List b, List c, List d,string cn)
{
OracleParameter op = null;
try
{
connection.Close();
connection.ConnectionString = cn;
connection.Open();
OracleCommand command = connection.CreateCommand();
command.CommandText = a;
command.CommandType = CommandType.StoredProcedure;
var arry = command.Parameters.Add("Accno", OracleDbType.Varchar2);
//op = new OracleParameter("Accno", OracleDbType.Varchar2);
arry.Direction = ParameterDirection.Input;
arry.CollectionType = OracleCollectionType.PLSQLAssociativeArray;
arry.Value = b.ToArray();
arry.Size = b.Count();
arry.ArrayBindSize = b.Select(_ => _.Length).ToArray();
arry.ArrayBindStatus = Enumerable.Repeat(OracleParameterStatus.Success, b.Count()).ToArray();
//op.Value = b;
//command.Parameters.Add(op);
var arry1 = command.Parameters.Add("Trnamount", OracleDbType.Double);
//op = new OracleParameter("Accno", OracleDbType.Varchar2); arry1.Direction = ParameterDirection.Input;
arry1.CollectionType = OracleCollectionType.PLSQLAssociativeArray;
arry1.Value = c.ToArray();
arry1.Size = c.Count();
arry1.ArrayBindSize = c.Select(_ => _.ToString().Length).ToArray();
arry1.ArrayBindStatus = Enumerable.Repeat(OracleParameterStatus.Success, c.Count()).ToArray();
//op.Value = b;
//command.Parameters.Add(op);
var arry2 = command.Parameters.Add("Trnno", OracleDbType.Int32);
//op = new OracleParameter("Accno", OracleDbType.Varchar2);
arry2.Direction = ParameterDirection.Input; arry2.CollectionType = OracleCollectionType.PLSQLAssociativeArray;
arry2.Value = d.ToArray();
arry2.Size = d.Count();
arry2.ArrayBindSize = d.Select(_ => _.ToString().Length).ToArray();
arry2.ArrayBindStatus = Enumerable.Repeat(OracleParameterStatus.Success, d.Count()).ToArray();
var arry3 = command.Parameters.Add("returnMessage", OracleDbType.Varchar2);
//op = new OracleParameter("Accno", OracleDbType.Varchar2);
arry3.Direction = ParameterDirection.Output;
arry3.CollectionType = OracleCollectionType.PLSQLAssociativeArray;
arry3.Size = d.Count();
arry3.ArrayBindSize = c.Select(_ => _.ToString().Length).ToArray();
arry3.ArrayBindStatus = Enumerable.Repeat(OracleParameterStatus.Success, d.Count()).ToArray();
int r = command.ExecuteNonQuery();
connection.Close();
List returnMessage = new List();
string[] returnMessage = (string[])(command.Parameters[4].Value);
return returnMessage.ToList();
}

Now I getting error on line

string[] returnMessage = (string[])(command.Parameters[4].Value);

Unable to cast object of type 'Oracle.DataAccess.Types.OracleString[]' to type 'System.String[]

So Please help me.

3 Replies

Know the answer? Post it — somebody with the same question will find it here.

Abhijit Ghosh9y ago
I heve tried but getting complilation error:

Can not convert type string to string[].
Rajkiran Swain9y ago
Convert.ToInt32((decimal)(OracleDecimal)(command.Parameters["4"].Value))
Rajkiran Swain9y ago
Can you please try this one

string[] returnMessage = (string[])(command.Parameters[4].Value).ToString());