1 Reply Latest reply: Jun 22, 2011 3:52 PM by 789008 RSS

    How to return a resultset using .Net SP

    856576
      Hi,

      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!
      Song
        • 1. Re: How to return a resultset using .Net SP
          789008
          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;
          try
          {
          cn.Open();
          da.Fill(ds);  ///FILL THE DATASET