.NET & Oracle Ref Cursor

[devchick25]

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]

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

 

[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).

 

[Sam Gentile [MVP-.NET/C#]]

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

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

 

[Harvey Flaisher]

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.

 

[Guest]

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

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

 

[Eric]

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