Using a ref cursor as input type with ODP.NET

Question

Hi all

I'm trying to use a RefCursor as an input parameter on an Oracle stored procedure. The idea is to select a group of records, feed them into the stored procedure and then the SP loops over the input RefCursor, doing some operations to its records. No, I can't select the records inside the SP and thus avoid having to use the RefCursor as an input type.

I've found an example on how to do this on (this here would be the link, but it seems I cannot use them yet) Oracle's documentation, but it uses a simple SELECT to populate the input RefCursor; and therein lies the rub: I've got to populate it from code.

You see, in code I have this:

[OracleDataParameter("P_INPUT", OracleDbType.RefCursor, ParameterDirection.Input)]
private List<MiObject> cursor;

and I've tried populating cursor with a List, a DataTable, even an plain array of MyObject, and nothing works. When I try running my tests I get an error: "Invalid Parameter Linking". (Maybe not the exact wording, I'm translating from Spanish, but that's the message).

Any ideas?

Answer 1

From memory, isn't there an OracleCursor class somewhere in the ODP.NET library that works?

Answer 2

I'm also in contact with Mark Williams, the author of the article I've tried to link on my post, and he has kinly responded like this:

" It is no problem to send me email; however, I think I will disappoint you with my answer on this one.

Unfortunately you can't do what you are trying to do (create a refcursor from the client like that).

A couple of problems with that are that a refcursor refers to memory owned by Oracle on the server and Oracle has no concept of client items like a DataTable or a .NET List, etc.

Do you have any other options available other than using a refcursor? "

So basically I'm screwed, and this question is closed. Thanks for reading and/or trying to help, you all.

Answer 3

Look at this sample for refcursor as input to pl/sql from oracle technet.

The clou is that the input refcursor object must be created by oracle themself. You cannot convert a list or anything else to refcursor.