locked
Getting Oracle Function return in C# Code RRS feed

  • Question

  • User433556381 posted

    Hi,

    I have a function in Oracle to return a VarChar2 value to my C# code behind. Problem is when I run my Function in the DataBase, it works fine but when I run my code to get the return value and display it, I do not get a value returned. Does anyone know how to handle this in C# please?? C# Code below. Thanks in Advance.

     

    private void btnSubmit_Click(object sender, EventArgs e)

    {

    try

    {

    conn =
    new OracleConnection(ConfigurationManager.ConnectionStrings["Info1"].ConnectionString);if (conn.State == ConnectionState.Closed)

    conn.Open();

    cmd = new OracleCommand("BEETLEDOME.GET_BETTLEDOME_MANAGER_NAME", conn);

    cmd.CommandType = CommandType.StoredProcedure;

    cmd.Parameters.Add("PERSONAL_B_EMP_ID", OracleDbType.Varchar2, "ADBSTE", ParameterDirection.Input);

    cmd.Parameters.Add("PERSONAL_MANAGERS_FULL_NAME", OracleDbType.Varchar2, ParameterDirection.ReturnValue);

    cmd.ExecuteNonQuery();

    string string_data = (string)((OracleString)(cmd.Parameters[0].Value)).Value;Response.Write("Returned from function: " + string_data);

    }

    catch (Exception ex)

    {

    lblMessage.Text = ex.Message;

    }

    finally

    {

    cmd.Dispose();

    conn.Dispose();

    conn.Close();

    }

    }

    Friday, January 25, 2008 3:31 PM

Answers

  • User281166198 posted

    Im not sure it will work, but try this (I use something like this) :

     string ReturnValue;

    ReturnValue =  cmd.ExecuteNonQuery();

    Good luck!
    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, January 29, 2008 2:34 PM

All replies

  • User887393078 posted

    Please put in the exact error message you get. also, verify whether your return value and pameter value are of same data type.

    Try to use odp.net, which can solve this problem

    Friday, January 25, 2008 4:35 PM
  • User433556381 posted

    I am using ODP.NET, the return value  of the function is VARCHAR2 and the problem is not from the Oracle side at all. Please look at my code above to see if I missed something in Syntax please.

    Friday, January 25, 2008 4:46 PM
  • User558493921 posted

    I m not sure of it. Plz change the Varchar to String You may get it.

    Plz correct me if i m wrong

    Thank u

    Baba

    Please remember to click "Mark as Answer" on this post if it helped you. 

    Monday, January 28, 2008 8:36 AM
  • User281166198 posted

    Im not sure it will work, but try this (I use something like this) :

     string ReturnValue;

    ReturnValue =  cmd.ExecuteNonQuery();

    Good luck!
    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, January 29, 2008 2:34 PM
  • User-801401005 posted

    Firstly, referencing parameters by ordinal is usually a very bad practice...

    >> cmd.Parameters[0].Value

    This will give you the first parameter added to the command which is your case is the parameter, not the return value.

    I expect the following code may work alot better for you...

    var mgrParam = cmd.Parameters.Add("PERSONAL_MANAGERS_FULL_NAME", OracleDbType.Varchar2, ParameterDirection.ReturnValue);

    cmd.ExecuteNonQuery();

    string string_data = (string)mgrParam.Value;

    Secondly, some Oracle providers behave differently, generally you want to set a size for the parameter, otherwise the drivers have trouble initialising buffers to store the return result. This may not be a problem in your case, but its a good habbit to get into.

    Hope this helps...

    Monday, August 1, 2011 11:41 PM