0 Replies Latest reply: Aug 11, 2008 9:42 AM by 654475 RSS

    how to use dataset with sys_refcursor at stored procedures

    654475
      i have a code like this but it doesnt work i dont know what can i do
      i am using c#

      this is the stored procedure

      -----------------------------------

      create or replace PROCEDURE Smart_Procedure_Price_List_LIS
      (
      v_pPriceListId IN NUMBER DEFAULT NULL ,
      cv_1 IN OUT SYS_REFCURSOR
      )
      AS
      BEGIN

      IF ( v_pPriceListId > 0 ) THEN
      BEGIN
      OPEN cv_1 FOR
      SELECT *
      FROM PriceList
      WHERE Id = v_pPriceListId;

      END;
      ELSE
      BEGIN
      OPEN cv_1 FOR
      SELECT *
      FROM PriceList ;

      END;
      END IF;

      END;

      ---------------------------------------


      and this is my c# code


      ------------------------------------


      OracleConnection con=new OracleConnection(connection);
      con.Open();

      OracleCommand cmd = new OracleCommand("declare a sys_refcursor; begin Smart_Procedure_Price_List_LIS(null,a); end;", con);


      OracleDataAdapter da = new OracleDataAdapter(cmd);
      System.Data.DataSet ds = new System.Data.DataSet();
      da.Fill(ds);
      string a = ds.Tables[0].Rows[0].ItemArray[0].ToString();


      ----------------------------------------------