Tech
Forums
Jobs
Books
Events
Interviews
Live
More
Learn
Training
Career
Members
Videos
News
Blogs
Login
Sign Up
Ask Question
3
Reply
Retrieve the Value of PLSQLAssociativeArray in C#
Abhijit Ghosh
Sep 4 2017 5:21 AM
214
Reply
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<Double> TrnAmt =
new
List<Double>();
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.
Upload Source Code
Select only zip and rar file.
Post
Reset
Cancel
Answers (
3
)
Next Recommended Forum
multiple recharge setup.
Need future Advice