Returning CURSORS from ORACLE STORED FUNCTIONS

[rpbinphilly]

Hi.
I have not been able to get a PLSQL stored FUNCTION to return a ref
cursor. (Stored Procedures work fine.)

I get a ORA-06550 return codes, and other similar messages that
indicate that the C# OracleParameter should be of Direction type =
Output. Event though a call to .DeriveParameters returns a param of
type ReturnValue...

It is a simple PLSQL function that returns a ref cursor and accepts no
input parameters.

I am using Oracle 10G and .NET Framework 1.1.

Has anyone else experienced problems with this? Are there any known
limitations to the Microsoft .NET Oracle Provider? Any good sources of
info?

Any and all enlightenment gratefully received.

Thanks in advance.

rpb

 

[Guest]

You can wrap the function in a sproc and get it working. Not a "great"
option, but it works.

To your question of compatibility: OracleClient is 8i compatible and has
some 9i functionality. Overall, I prefer ODP.NET (otn.oracle.com - free
download) to OracleClient for all but simple Oracle stuff.


---

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

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

 

[rpbinphilly]

But the gist of this is = "Stored Functions are NOT supported via the
Microsoft .NET PRovider? Is that documented anywhere?

Thanks very much.

 

[Eric]

But the gist of this is = "Stored Functions are NOT supported via the
Microsoft .NET PRovider? Is that documented anywhere?

http://groups-beta.google.com/group...racle+function"&rnum=1&hl=en#3e4cc050d0df3961

I STRONGLY recommend that you use ODP with the 10g database. There's a
*LOT* of new Oracle features that the MS provider can't support.

You should also check out the beta ODP for use with VS 2005.

For earlier versions of Oracle I haven't found a compelling reason to
use ODP. This function issue isn't that big of a deal, since there's an
easy work-around as Cowboy said.

Eric

 

[Guest]

Untrue. Stored functions can be called, the limitation is that you can't
access a ref cursor that comes back. With numbers/varchar2 etc... it works
fine as long as you add an extra parameter with the direction being
returnvalue.

regards,

mr fox.