Forum Stats

  • 3,836,792 Users
  • 2,262,193 Discussions
  • 7,900,114 Comments

Discussions

How to return a resultset using .Net SP

856576
856576 Member Posts: 1
edited Jun 22, 2011 4:52PM in .NET Stored Procedures
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

Answers

  • 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;
    try
    {
    cn.Open();
    da.Fill(ds); ///FILL THE DATASET
This discussion has been closed.