.NET & Oracle Ref Cursor -- dotnet faqs

anonymous NO[at]SPAM discussions.microsoft.com
12/17/2004 5:14:19 AM

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

[quoted text, click to view]

Bishoy Ghaly
12/17/2004 1:43:01 PM

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

[quoted text, click to view]

Sam Gentile [MVP-.NET/C#]
1/5/2005 1:24:48 AM

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

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

[quoted text, click to view]

Harvey Flaisher
1/6/2005 5:06:34 AM

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 ***

Prashant K
1/21/2005 9:31:01 PM

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
1/22/2005 10:51:19 AM

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.

Got something to say? Click here to post a reply to this thread.

Don't see what you're looking for? Try a search.

View other messages about dotnet faqs

dotnet faqs : .NET & Oracle Ref Cursor