Returning an Oracle cursor

[Roger Moore]

I need to return a cursor from an Oracle package using OleDb (we currently
do not have the Oracle provider installed), but I haven't yet found a clear
explanantion of how to do it. I have the usual stored procedure code in my
ASP.NET page, like so:

objConn = New OleDbConnection(Application("dbString"))
objComm = New OleDbCommand(strSQL, objConn)
objComm.CommandType = CommandType.StoredProcedure
objParam = New OleDbParameter()

objParam = objComm.Parameters.Add("AddressID",
OleDbType.Numeric, 7)
objParam.Direction = ParameterDirection.Input
objParam.Value = strTemp

objParam = objComm.Parameters.Add("addressName",
OleDbType.VarChar, 50)
objParam.Direction = ParameterDirection.ReturnValue

objConn.Open()
objComm.ExecuteScalar()

How do I go about retrieving the cusor into a DataSet or DataReader?

Thank you,
Roger

 

[Paul Clement]

¤ I need to return a cursor from an Oracle package using OleDb (we currently
¤ do not have the Oracle provider installed), but I haven't yet found a clear
¤ explanantion of how to do it. I have the usual stored procedure code in my
¤ ASP.NET page, like so:
¤
¤ objConn = New OleDbConnection(Application("dbString"))
¤ objComm = New OleDbCommand(strSQL, objConn)
¤ objComm.CommandType = CommandType.StoredProcedure
¤ objParam = New OleDbParameter()
¤
¤ objParam = objComm.Parameters.Add("AddressID",
¤ OleDbType.Numeric, 7)
¤ objParam.Direction = ParameterDirection.Input
¤ objParam.Value = strTemp
¤
¤ objParam = objComm.Parameters.Add("addressName",
¤ OleDbType.VarChar, 50)
¤ objParam.Direction = ParameterDirection.ReturnValue
¤
¤ objConn.Open()
¤ objComm.ExecuteScalar()
¤
¤ How do I go about retrieving the cusor into a DataSet or DataReader?

I don't believe ref cursors are supported using OLEDB and ADO.NET. I would seriously consider moving
to the managed provider, which supports them directly. The OLEDB provider is essentially obsolete
for .NET and it was developed for an earlier version of the OCI layer - lacking support for newer
Oracle data types and features.


Paul ~~~ (e-mail address removed)
Microsoft MVP (Visual Basic)