Forum Stats

  • 3,836,792 Users
  • 2,262,193 Discussions


How to return a resultset using .Net SP

856576 Member Posts: 1
edited Jun 22, 2011 4:52PM in .NET Stored Procedures

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!


  • SURFThru
    SURFThru Member Posts: 24
    edited Jun 22, 2011 4:52PM
    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
This discussion has been closed.