1 Reply Latest reply on Jun 22, 2011 8:52 PM by 789008

    How to return a resultset using .Net SP


      I had a problem with using the .Net Stored Procedures.

      How can i get the query resultset from a .Net SP?
      DataSet and ref CURSOR doesn't work, because they can not be a parameter or return.

      My Oracle is 10gR2,and I'm using Visual Studio 2005

      Are there any ways to do that??

      Thanks for your help!
        • 1. Re: How to return a resultset using .Net SP
          If you use the Oracle Data Provider you have access to the RefCursor. BUT you do not need to access that in .NET. You can just FILL your Dataset from the Data Adapater. Here is some code...

          //Call the procedure

          OracleConnection cn = new OracleConnection(Classes.GeneralMethods.GetConnectionString("userid=xxxxxx");
          OracleCommand cmd = new OracleCommand("pkg_ora_package.prc_get_my_data", cn);
          cmd.CommandType = CommandType.StoredProcedure;

          //Bind params by name and not by position.
          cmd.BindByName = true;

          cmd.Parameters.Add(OParm.GetOracleParameter("iv_acct_no", ParameterDirection.Input, iv_acct_no));
          cmd.Parameters.Add(OParm.GetOracleParameter("ocsr", OracleDbType.RefCursor, ParameterDirection.Output));

          DataSet ds = new DataSet();
          OracleDataAdapter da = new OracleDataAdapter();
          da.SelectCommand = cmd;
          da.Fill(ds);  ///FILL THE DATASET