Click here to Skip to main content
15,889,216 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
I have an Oracle function that receives a status and returns another status, that function can not be modified and I wanted to know if someone has a better idea of how iterating the same function passing it a status list.

Thanks.


C#
public List<string> GetStatusNotDisplayTCByList(List<string> status)
       {
           try
           {
               List<string> result = new List<string>();

               using (OracleConnection conn = new OracleConnection(ConfigurationManager.ConnectionStrings["dbnet"].ConnectionString))
               {
                   using (OracleCommand comm = new OracleCommand("pkg_aut_module.fn_get_status", conn))
                   {
                       comm.CommandType = CommandType.StoredProcedure;
                       comm.BindByName = true;
                       comm.ArrayBindCount = status.Count;
                       comm.Parameters.Add("p_status", OracleDbType.Varchar2, status.ToArray(),ParameterDirection.Input);
                       comm.Parameters.Add("Vli", OracleDbType.RefCursor, null, ParameterDirection.ReturnValue);
                       DataTable dt = new DataTable();
                       conn.Open();

                       using (OracleDataReader reader = comm.ExecuteReader())
                       {
                           if (reader.HasRows)
                           {
                               dt.Load(reader);

                               List<DataRow> dr = dt.AsEnumerable().ToList();

                               while (reader.Read())
                               {
                                   foreach (var row in dr.AsEnumerable())
                                   {
                                       result.Add(Convert.ToString(row["status_display"]));
                                   }
                               }

                               reader.Close();
                               conn.Dispose();
                           }
                       }
                   }
               }

               return result;
           }
           catch (Exception e)
           {

               throw;
           }
       }


What I have tried:

I am trying to pass more than one parameter to an Oracle function and if it was not possible I wanted to know how I could call the function several times in an effective way.
Posted
Updated 12-Jul-17 5:11am
v2

1 solution

There are ways to do it if your proc has an output parameter. If not then you'll just have to loop
 
Share this answer
 
Comments
Laxmax1 14-Jul-17 13:30pm    
I solved it, i create a method that calls the function in a foreach, did not want to do so but in the end it works. thank you for your help.
Andy Lanng 14-Jul-17 14:48pm    
Without changing the routine, it was the only choice. Well done for getting through it and thanks for accepting my disappointing answer 😊

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900