Calling Oracle Stored Procs

[JV]

A ref cursor data type can obviously be returned as an output parameter of a
stored procedure, but can an ASP.NET application call an oracle proc that
uses a ref cursor as an input parameter? If so, how? What object do you
pass as that parameter?

We are using System.Data.OracleClient, but if necessary, we might be able to
switch.

 

[Guest]

Hi Dear JV,

here are the some links that may be useful which gives how to call oracle
stored procedure from ado.net in turn asp.net and also how to use ref cursor
with DataReader and some general features and limitations.

How To Return an Oracle Ref Cursor to a .NET DataReader Object by Using the
..NET Managed Provider for Oracle
==============================================================================================================================================

http://support.microsoft.com/default.aspx?scid=kb;en-us;322160


How to call Oracle stored procedures in Visual Basic .NET with the Microsoft
Oracle Managed Provider
=======================================================================
http://support.microsoft.com/default.aspx?scid=kb;en-us;321718


Features and limitations of the .NET Managed Provider for Oracle
=================================================================
http://support.microsoft.com/default.aspx?scid=kb;en-us;322158


***********************************************************************
Mastering .NET Application Development with Oracle
Optimize Result Set Retrieval Using ODP.NET and Ref Cursors
by Mark A. Williams
===============================================================
http://www.oracle.com/technology/pub/articles/mastering_dotnet_oracle/williams_refcursors.html

Bye
Venkat_KL

 

[JV]

Thanks, but I think you missed the point of the question. I already know
how to RETRIEVE a cursor parameter (parameterdirection.out). The question
is can ASP.NET pass IN a cursor.

--John

 

[Cowboy \(Gregory A. Beamer\)]

When you use the OleDb providers, you can simply consume the returned ref
cursor as a DataTable.

With the OracleClient provider, you have to specify the ref_cursor as an
output parameter. There is no need to actually consume this parameter, as
you will receive your output through normal means once you send in the
properly named ref cursor output parameter, but the param has to be there to
link the MS world with the Oracle world.

If I remember correctly, the same is true with ODP.NET.

--
Gregory A. Beamer
MVP; MCP: +I, SE, SD, DBA

***********************************************
Think Outside the Box!
***********************************************

 

[Cowboy \(Gregory A. Beamer\)]

The .NET Managed Provider for Oracle supports binding Ref_cursors as output
parameters only. There is no support for Ref_cursors as an input parameter.

--
Gregory A. Beamer
MVP; MCP: +I, SE, SD, DBA

***********************************************
Think Outside the Box!
***********************************************

 

[JV]

Thanks, Greg. That was what I expected, but couldn't find actual
information about it.

 

[Guest]

Hi Dear JV,

one more useful link

How To Retrieve Multiple Ref_cursors from an Oracle Stored Procedure by
Using the .NET Managed Provider For Oracle
==========================================================================================================

http://support.microsoft.com/kb/321715/

bye
Venkat_KL