Abhijit Ghosh

Abhijit Ghosh

  • 1.8k
  • 27
  • 2.1k

Retrieve the Value of PLSQLAssociativeArray in C#

Sep 4 2017 5:21 AM
Actually my code pass various type of List array as a parameter to Oracle by using Oracle Stored Procedure. The Code Given
  1. List AccNo = new List();  
  2. List<Double> TrnAmt = new List<Double>();  
  3. List TranNo = new List();  
  4. List rec = new List();  
  5. AccNo.Add(data_read3[3].ToString());  
  6. TrnAmt.Add( Double.Parse(data_read3[0].ToString()));  
  7. 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
 
  1. public List Call_Procedure_Host(string a, List b, List c, List d,string cn)  
  2. {  
  3. OracleParameter op = null;  
  4. try  
  5. {  
  6. connection.Close();  
  7. connection.ConnectionString = cn;  
  8. connection.Open();  
  9. OracleCommand command = connection.CreateCommand();  
  10. command.CommandText = a;  
  11. command.CommandType = CommandType.StoredProcedure;  
  12. var arry = command.Parameters.Add("Accno", OracleDbType.Varchar2);  
  13. //op = new OracleParameter("Accno", OracleDbType.Varchar2);  
  14. arry.Direction = ParameterDirection.Input;  
  15. arry.CollectionType = OracleCollectionType.PLSQLAssociativeArray;  
  16. arry.Value = b.ToArray();  
  17. arry.Size = b.Count();  
  18. arry.ArrayBindSize = b.Select(_ => _.Length).ToArray();  
  19. arry.ArrayBindStatus = Enumerable.Repeat(OracleParameterStatus.Success, b.Count()).ToArray();  
  20. //op.Value = b;  
  21. //command.Parameters.Add(op);  
  22. var arry1 = command.Parameters.Add("Trnamount", OracleDbType.Double);  
  23. //op = new OracleParameter("Accno", OracleDbType.Varchar2); arry1.Direction = ParameterDirection.Input;  
  24. arry1.CollectionType = OracleCollectionType.PLSQLAssociativeArray;  
  25. arry1.Value = c.ToArray();  
  26. arry1.Size = c.Count();  
  27. arry1.ArrayBindSize = c.Select(_ => _.ToString().Length).ToArray();  
  28. arry1.ArrayBindStatus = Enumerable.Repeat(OracleParameterStatus.Success, c.Count()).ToArray();  
  29. //op.Value = b;  
  30. //command.Parameters.Add(op);  
  31. var arry2 = command.Parameters.Add("Trnno", OracleDbType.Int32);  
  32. //op = new OracleParameter("Accno", OracleDbType.Varchar2);  
  33. arry2.Direction = ParameterDirection.Input; arry2.CollectionType = OracleCollectionType.PLSQLAssociativeArray;  
  34. arry2.Value = d.ToArray();  
  35. arry2.Size = d.Count();  
  36. arry2.ArrayBindSize = d.Select(_ => _.ToString().Length).ToArray();  
  37. arry2.ArrayBindStatus = Enumerable.Repeat(OracleParameterStatus.Success, d.Count()).ToArray();  
  38. var arry3 = command.Parameters.Add("returnMessage", OracleDbType.Varchar2);  
  39. //op = new OracleParameter("Accno", OracleDbType.Varchar2);  
  40. arry3.Direction = ParameterDirection.Output;  
  41. arry3.CollectionType = OracleCollectionType.PLSQLAssociativeArray;  
  42. arry3.Size = d.Count();  
  43. arry3.ArrayBindSize = c.Select(_ => _.ToString().Length).ToArray();  
  44. arry3.ArrayBindStatus = Enumerable.Repeat(OracleParameterStatus.Success, d.Count()).ToArray();  
  45. int r = command.ExecuteNonQuery();  
  46. connection.Close();  
  47. List returnMessage = new List();  
  48. string[] returnMessage = (string[])(command.Parameters[4].Value);  
  49. return returnMessage.ToList();  
  50. }  
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.

Answers (3)