Hello,

I am wondering if anyone in this community knows the shorter way to return an Oracle Ref Cursor to a .NET DataReader object by using the .NET Managed Provider for Oracle, something like

return (IDataReader) SqlHelper.ExecuteReader(ConnectionString, "GET_ACCOUNT", Id);

instead of using the way below (look at the C# code)

Stored Procedure

 
create or replace PROCEDURE "GET_ACCOUNT"
(
	p_ID IN ACCOUNT.ID%type, retCursor OUT SA.MYGEN.sqlcur
)
IS
BEGIN
    OPEN retCursor FOR
	SELECT
		ID,
		NAME
	FROM ACCOUNT
	WHERE
		ID = p_ID
;
END GET_ACCOUNT;
 

Code (C#)

OracleConnection OraConn = new OracleConnection(_connectionString);
OraConn.Open();

OracleCommand OraCmd = new OracleCommand();
OraCmd.Connection = OraConn;
OraCmd.CommandText = "GET_ACCOUNT";
OraCmd.CommandType = CommandType.StoredProcedure;
OraCmd.Parameters.Add(new OracleParameter("retCursor", OracleType.Cursor)).Direction = ParameterDirection.Output;
OraCmd.Parameters.Add("p_ID", OracleType.Number, 10).Value = Id;

OraCmd.ExecuteNonQuery();
return (IDataReader)OraCmd.Parameters["retCursor"].Value;

Thank you.

cheers,

Eric

A while back I had to do a project using Oracle and VB... it was fun, because I am used to MS SQL and C#. Anyhow, to make my life easier, I wrote this little datasource to work with Oracle "Get" type stored procedures:

Imports System
Imports System.Data
Imports System.Data.OracleClient
Imports System.ComponentModel

    < _
    ToolboxData("<{0}:OracleDataSource runat=server></{0}:OracleDataSource>") _
    > _
    Public Class OracleDataSource
        Inherits SqlDataSource

        Private _AddOutputCursor As Boolean = True
        Private _SelectCursorName As String = "p_Cursor"
        Private _SchemaName As String = ""

        < _
        Category("Data"), _
        Browsable(True), _
        Bindable(False), _
        DefaultValue(GetType(Boolean), "False"), _
        Description("Add a ref cursor output parameter to Select Stored Procedure Commands"), _
        PersistenceMode(PersistenceMode.Attribute) _
        > _
        Public Property AddOutputCursor() As Boolean
            Get
                Return _AddOutputCursor
            End Get
            Set(ByVal value As Boolean)
                _AddOutputCursor = value
            End Set
        End Property

        < _
        Category("Data"), _
        Browsable(True), _
        Bindable(False), _
        DefaultValue("p_Cursor"), _
        Description("Cursor Parameter Name for Select Stored Procedure"), _
        PersistenceMode(PersistenceMode.Attribute) _
        > _
        Public Property SelectCursorName() As String
            Get
                Return _SelectCursorName
            End Get
            Set(ByVal value As String)
                _SelectCursorName = value
            End Set
        End Property

        < _
        Category("Data"), _
        Browsable(True), _
        Bindable(False), _
        DefaultValue(""), _
        Description("Default schema to use for database access"), _
        PersistenceMode(PersistenceMode.Attribute) _
        > _
        Public Property SchemaName() As String
            Get
                Return _SchemaName
            End Get
            Set(ByVal value As String)
                _SchemaName = value
            End Set
        End Property

        Protected Overrides Sub OnInit(ByVal e As System.EventArgs)
            MyBase.OnInit(e)
            Dim view As SqlDataSourceView
            view = DirectCast(GetView("DefaultView"), SqlDataSourceView)
            AddHandler view.Selecting, AddressOf OnSelecting
            AddHandler view.Inserting, AddressOf OnCommand
            AddHandler view.Updating, AddressOf OnCommand
            AddHandler view.Deleting, AddressOf OnCommand
        End Sub

        Protected Sub OnSelecting(ByVal sender As Object, ByVal e As SqlDataSourceSelectingEventArgs)
            If (e.Command.CommandType = CommandType.StoredProcedure) Then
                If SchemaName <> String.Empty Then
                    e.Command.CommandText = SchemaName + "." + e.Command.CommandText
                End If
                If AddOutputCursor Then
                    Dim dbParam As New OracleParameter()
                    dbParam.OracleType = OracleType.Cursor
                    dbParam.Direction = ParameterDirection.Output
                    dbParam.ParameterName = _SelectCursorName
                    e.Command.Parameters.Add(dbParam)
                End If
            End If
        End Sub

        Protected Sub OnCommand(ByVal sender As Object, ByVal e As SqlDataSourceCommandEventArgs)
            If (e.Command.CommandType = CommandType.StoredProcedure) Then
                If SchemaName <> String.Empty Then
                    e.Command.CommandText = SchemaName + "." + e.Command.CommandText
                End If
            End If
        End Sub

    End Class

I was able to directly substitute this for the SqlDataSource for all my controls. All the "Get" type stored procedures were written like "open p_cursor for ...", and that worked fine, no need to declare a bunch of cursor types.

Hi,

 You can use like this:

OracleDataReader dr;

dr=OraCmd.Parameters["retCursor"].Value;

Return this dr;

Hope this helps

If you are using ODP.net ( oracle's managed provider for oracle ) rather then ado.net ( microsoft's managed provider for oracle) you have to do it the way you are doing it.

If you are using ado.net then you can do a ExecuteReader. You have to pass in the RefCursor as a parameter either way. 

Here is an msdn artical that shows ExecuteReader with RefCursors.

http://msdn2.microsoft.com/en-us/library/hsd6yxkc(VS.80).aspx

I believe if you use enterprise library it will dynamically determine the refcursor parameters and you do not have to write code to add them.

This thread talks about it

http://forums.asp.net/t/883771.aspx

I think the down side is that it can only return one ref cursor at a time and you have to call it cur_out

Hi guys,

Thank you very much for all the inputs, I appreciate it very much. I will look into all your suggestions.

Thanks!!!

cheers,

~eric~