Retrieve the vales of PLSQLAssociativeArray In C#

Question

I am getting the error of the following line returnMessage = ((command.Parameters[4].Value);

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

List<string> AccNo = new List<string>();
List<Double> TrnAmt = new List<Double>();
List<int> TranNo = new List<int>();
List<string> rec = new List<string>();

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<string> Call_Procedure_Host(string a, List<string> b, List<double> c, List<int> 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<string> returnMessage = new List<string>();
                    returnMessage = ((command.Parameters[4].Value);                                    
        return returnMessage;


    }

Now I getting error on line returnMessage = ((command.Parameters[4].Value);

Error CS0266 Cannot implicitly convert type 'object' to 'System.Collections.Generic.List'. An explicit conversion exists (are you missing a cast?)

Please help me to resolve the issue.

Luke Woodward · Accepted Answer · 2017-09-04T19:20:49.603

Your code is failing to compile because the compile-time type of command.Parameters[4].Value is object.

If your stored procedure returns a PL/SQL associative array of strings in an output parameter, then ODP.NET will set the value of the parameter to a C# array after the stored-procedure call completes. You need to then get that array and convert it to a List<string>.

If you are using Oracle Managed Data Access (Oracle.ManagedDataAccess.dll), the array is an array of C# strings. To get this into a List<string>, use this:

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

If you are using the non-managed driver (Oracle.DataAccess.dll), the array comes back as an array of OracleStrings. Getting the strings out of this is sadly more work:

    OracleString[] returnMessage = (OracleString[])(command.Parameters[4].Value);
    List<string> strings = returnMessage.Select(os => os.IsNull ? null : os.Value).ToList();
    return strings

Unable to cast object of type 'Oracle.DataAccess.Types.OracleString[]' to type 'System.String[] — Abhijit Ghosh, Sep 04 '17 at 05:29
@AbhijitGhosh: sorry about that, the code I presented works with the managed driver but it seem you're not using that. I've edited my question to include how to read the strings out of an `OracleString` array, please let me know if that doesn't work. — Luke Woodward, Sep 04 '17 at 19:23

Retrieve the vales of PLSQLAssociativeArray In C#

1 Answers1

Linked