CODEWITHSUNDEEP
c#oracle-databaseplsql
OddCommand24
OddCommand24

Reputation: 441

How to call a function from Oracle on C#?

I want to execute the following Oracle function and return its value in a C# method:

CREATE OR REPLACE Function days (fulldays int, halfdays int)
return int is daysResult int; 
BEGIN  
daysResult:= ((fulldays * 8) + (halfdays * 4)); 
return (daysResult);
END days;

C# code:

 public double days(int fulldays, int halfdays)
        {
                OracleCommand cmd = conn.CreateCommand();
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.CommandText = "days";
                cmd.Parameters.Add("fulldays", fulldays);
                cmd.Parameters.Add("halfdays", halfdays);
                cmd.Parameters.Add("daysResult", OracleDbType.Int32);
                cmd.Parameters["daysResult"].Direction = ParameterDirection.ReturnValue;
                cmd.Connection.Open();
                cmd.ExecuteNonQuery();
                var result = Convert.ToString(cmd.Parameters["daysResult"].Value);
                MessageBox.Show(result); //----shows null value
                return Convert.ToDouble(result);
        }

I want to retrieve the value returned in the Oracle function and return it to the C# method as well, but the MessageBox ends up showing blank whenever C# 'days' method is called.

Upvotes: 0

Views: 239

Answers (1)

Wernfried Domscheit
Wernfried Domscheit

Reputation: 59652

The order of parameters is wrong. The return parameter must be the first one:

cmd.Parameters.Add("daysResult", OracleDbType.Int32);
cmd.Parameters["daysResult"].Direction = ParameterDirection.ReturnValue;
cmd.Parameters.Add("fulldays", fulldays);
cmd.Parameters.Add("halfdays", halfdays);

Upvotes: 2

Related Questions