3 Replies Latest reply: Mar 20, 2013 9:36 AM by 998035 RSS

    Trying to call a function from C#

    998035
      Hi all,

      I am having problems calling a function that returns a sys refcursor and takes a varchar2 as an input. From the SQL Developer IDE I see that the function is run like

      DECLARE
      X_AR_MHTR VARCHAR2(200);
      v_Return sys_refcursor;
      BEGIN
      X_AR_MHTR := 697678;

      v_Return := CMS_SMS.FOR_WEB.WEB_STATION_1(
      X_AR_MHTR => X_AR_MHTR
      );
      /* Legacy output:
      DBMS_OUTPUT.PUT_LINE('v_Return = ' || v_Return);
      */
      :v_Return := v_Return; --<-- Cursor
      END;


      here is my C# code

      public static void getTilemetriseis(bool isLemessos, string AMSet, out OracleDataReader dr)
      {
      DataSet ds = new DataSet();
      OracleDataAdapter ad = new OracleDataAdapter();
      OracleCommand cmd = new OracleCommand();
      cmd.CommandType = CommandType.StoredProcedure;
      cmd.CommandText = "CMS_SMS.FOR_WEB.WEB_Station_1";

      if (isLemessos)
      {
      cmd.Parameters.Add("X_AR_MHTR", OracleDbType.Varchar2, ConfigurationManager.AppSettings["LemessosArMhtr"].ToString(), ParameterDirection.Input);
      cmd.Parameters.Add(new OracleParameter("anexoflCur", OracleDbType.RefCursor, ParameterDirection.ReturnValue));
      // ad.SelectCommand.CommandText = string.Format(@"select m_code,ar_mhtr,city,address,stcode
      // from cms_sms.FOR_WEB.flowmetersall where ar_mhtr = {0}
      // order by length(ar_mhtr),ar_mhtr", ConfigurationManager.AppSettings["LemessosArMhtr"].ToString());
      }
      else
      {
      cmd.Parameters.Add("X_AR_MHTR", OracleDbType.Varchar2, "697678", ParameterDirection.Input);
      cmd.Parameters.Add(new OracleParameter("v_Return", OracleDbType.RefCursor, ParameterDirection.ReturnValue));
      // ad.SelectCommand.CommandText = string.Format(@"select m_code,ar_mhtr,city,address,stcode
      // from cms_sms.FOR_WEB.flowmetersall where ar_mhtr= -1
      // order by length(ar_mhtr),ar_mhtr", AMSet);
      }
      cmd.Connection = OracleDBManager.getOracleCMSConnection();
      cmd.ExecuteNonQuery();
      dr = (OracleDataReader)cmd.Parameters["v_Return"].Value;
      cmd.Connection.Close();
      }

      The error rises on cmd.ExecuteNonQuery() and is described as

      ORA-06550:
      PLS-00306: wrong number or type of argument

      Any ideas?
        • 1. Re: Trying to call a function from C#
          Tridus
          Off the top of my head, your parameter type on the second parameter is wrong. The function doesn't have a return value, one of the parameters contains it. So the second parameter's type can't be ReturnValue (should be output).

          Either that or change the function to only have one parameter and add RETURN sys_refcursor into the definition, at which point you can use ReturnValue.

          Here's an example: http://stackoverflow.com/questions/1773534/what-is-the-right-way-to-call-an-oracle-stored-function-from-ado-net-and-get-the

          Edited by: Tridus on Mar 20, 2013 10:24 AM
          • 2. Re: Trying to call a function from C#
            998035
            Let me clarify. This is not the actual function, its the code produced when I try to run the function from SQL Developer. Also, I am doing things as described in your example in the case of having a cursor
            • 3. Re: Trying to call a function from C#
              998035
              I actually solved the problem....Just had to put the return value parameter first instead of second