.NET & Oracle Ref Cursor

Discussion in 'ASP .Net' started by devchick25, Dec 16, 2004.

  1. devchick25

    devchick25 Guest

    Hi,

    I am creating a service that uses an Oracle backend. I
    need to use a stored procedure/package to return a
    resultset from Oracle. In other words, I cannot use a
    direct select in the .NET code. The resultset may be
    zero, one, or many rows. Therefore, I have looked into
    using something that Oracle refers to as a ref cursor.

    Is the ref cursor what I'm looking for?

    How does .NET return a stored procedure that uses a ref
    cursor? Is it a result set? Any explanation or examples
    would be greatly appreciated.

    Thank you!
     
    devchick25, Dec 16, 2004
    #1
    1. Advertising

  2. devchick25

    Bishoy Ghaly Guest

    in .NET why dont you use standard OracleClient Connection and command and
    DataAdapter to return a DataSet of the result rows ?

    "devchick25" <> wrote in message
    news:0b5f01c4e3c7$8c91f7f0$...
    > Hi,
    >
    > I am creating a service that uses an Oracle backend. I
    > need to use a stored procedure/package to return a
    > resultset from Oracle. In other words, I cannot use a
    > direct select in the .NET code. The resultset may be
    > zero, one, or many rows. Therefore, I have looked into
    > using something that Oracle refers to as a ref cursor.
    >
    > Is the ref cursor what I'm looking for?
    >
    > How does .NET return a stored procedure that uses a ref
    > cursor? Is it a result set? Any explanation or examples
    > would be greatly appreciated.
    >
    > Thank you!
     
    Bishoy Ghaly, Dec 17, 2004
    #2
    1. Advertising

  3. devchick25

    Guest Guest

    We would like to store the sql statement outside of
    the .NET application. We were originally thinking that a
    stored procedure would be the best option (so that we
    wouldn't have to change the .NET code if we needed to only
    change the SQL statement).

    if we could use the ref cursor, I think that would be the
    best option for us. But I'm not sure about the efficiency
    and what I need to do in .NET to make this work. (I'm a
    newbie to .NET).

    >-----Original Message-----
    >in .NET why dont you use standard OracleClient Connection

    and command and
    >DataAdapter to return a DataSet of the result rows ?
    >
    >"devchick25" <> wrote

    in message
    >news:0b5f01c4e3c7$8c91f7f0$...
    >> Hi,
    >>
    >> I am creating a service that uses an Oracle backend. I
    >> need to use a stored procedure/package to return a
    >> resultset from Oracle. In other words, I cannot use a
    >> direct select in the .NET code. The resultset may be
    >> zero, one, or many rows. Therefore, I have looked into
    >> using something that Oracle refers to as a ref cursor.
    >>
    >> Is the ref cursor what I'm looking for?
    >>
    >> How does .NET return a stored procedure that uses a ref
    >> cursor? Is it a result set? Any explanation or

    examples
    >> would be greatly appreciated.
    >>
    >> Thank you!

    >
    >
    >.
    >
     
    Guest, Dec 17, 2004
    #3
  4. You may want to look at Oracle's value-add managed provider ODP.NET that
    incorporates support for Ref Cursor and other "advanced" Oracle;e features
    over System.Data.Oracle.

    Otherwise, the standard thing to do is use the OracleClient Connection and
    then the DataSet as suggested
    --------------------
    ------------------------------------

    Adesso Systems

    Sam Gentile

    Chief Architect

    http://samgentile.com/blog/

    Microsoft MVP - C#/.NET, INETA Speaker, Beantown.NET UG Leader

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

    <> wrote in message
    news:00ac01c4e43a$5128f010$...
    > We would like to store the sql statement outside of
    > the .NET application. We were originally thinking that a
    > stored procedure would be the best option (so that we
    > wouldn't have to change the .NET code if we needed to only
    > change the SQL statement).
    >
    > if we could use the ref cursor, I think that would be the
    > best option for us. But I'm not sure about the efficiency
    > and what I need to do in .NET to make this work. (I'm a
    > newbie to .NET).
    >
    >>-----Original Message-----
    >>in .NET why dont you use standard OracleClient Connection

    > and command and
    >>DataAdapter to return a DataSet of the result rows ?
    >>
    >>"devchick25" <> wrote

    > in message
    >>news:0b5f01c4e3c7$8c91f7f0$...
    >>> Hi,
    >>>
    >>> I am creating a service that uses an Oracle backend. I
    >>> need to use a stored procedure/package to return a
    >>> resultset from Oracle. In other words, I cannot use a
    >>> direct select in the .NET code. The resultset may be
    >>> zero, one, or many rows. Therefore, I have looked into
    >>> using something that Oracle refers to as a ref cursor.
    >>>
    >>> Is the ref cursor what I'm looking for?
    >>>
    >>> How does .NET return a stored procedure that uses a ref
    >>> cursor? Is it a result set? Any explanation or

    > examples
    >>> would be greatly appreciated.
    >>>
    >>> Thank you!

    >>
    >>
    >>.
    >>
     
    Sam Gentile [MVP-.NET/C#], Jan 5, 2005
    #4
  5. Just my 2 cents - I believe the original idea is correct - one should
    never embed sql statements in code these days due to code security
    issues. this is standard Microsoft practice and recommendation. Use of
    stored procs is always recommended, even for simple queries.

    *** Sent via Developersdex http://www.developersdex.com ***
    Don't just participate in USENET...get rewarded for it!
     
    Harvey Flaisher, Jan 6, 2005
    #5
  6. Hi,

    Assume this is the solution you require(its in vb.net)

    '//////////////////////////////////////

    Dim Ds As New DataSet()
    Dim Oraclecon As New OracleConnection("Data Source=YourOracle;User
    ID=scott;Password=tiger")

    Oraclecon.Open()


    Dim myCMD As New OracleCommand()
    myCMD.Connection = Oraclecon
    myCMD.CommandText = "multiRefCursors.EmpDept"
    myCMD.CommandType = CommandType.StoredProcedure
    myCMD.Parameters.Add(New OracleParameter("io_cursor1",
    OracleType.Cursor)).Direction = ParameterDirection.Output
    myCMD.Parameters.Add(New OracleParameter("io_cursor2",
    OracleType.Cursor)).Direction = ParameterDirection.Output

    Dim MyDA As New OracleDataAdapter(myCMD)

    Try
    MyDA.Fill(Ds)
    Catch Myex As Exception
    MessageBox.Show(Myex.Message.ToString)
    End Try


    Ds.Relations.Add("EmpDept", Ds.Tables(0).Columns("Deptno"),
    Ds.Tables(1).Columns("Deptno"))
    DataGrid1.DataSource = Ds.Tables(0)
    Oraclecon.Close()
    '////////////////////////////////////

    Go to http://support.microsoft.com/default.aspx?scid=kb;en-us;321715 for
    more detailed information

    '//////////////////////////
     
    =?Utf-8?B?UHJhc2hhbnQgSw==?=, Jan 22, 2005
    #6
  7. devchick25

    Eric Guest

    Ref cursors definitely work, but I found them to be much slower than
    using a conventional select. I only use them when I have to because an
    existing stored proc only sends output to a ref cursor.

    Eric
     
    Eric, Jan 22, 2005
    #7
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. devchick25

    .NET & Oracle Ref Cursor

    devchick25, Dec 16, 2004, in forum: ASP .Net
    Replies:
    0
    Views:
    680
    devchick25
    Dec 16, 2004
  2. Replies:
    0
    Views:
    341
  3. Replies:
    22
    Views:
    763
    peter koch
    Apr 30, 2008
  4. Replies:
    6
    Views:
    352
    James Kanze
    Apr 29, 2008
  5. dmaziuk
    Replies:
    3
    Views:
    582
    Chris Gonnerman
    Jan 25, 2011
Loading...

Share This Page