Retrieving RefCursor and VarChar Data from the Same Stored Procedure

Question

I am new to Oracle, so please be careful ...

I am accessing an existing Oracle database using C # and ODP11 in .NET 4. All the procedures up to this point were, well, painful to understand at first, but basically "just worked" as soon as I realized what I was doing ... this is a different story ...

I have a saved proc that takes 4 parameters: 2 inputs and 2 outputs ... 2 inputs are great. one of the outputs is Varchar2 and the other is refcursor.

I use OracleDataReader to run a query, for example, I have any other proc that returns a RefCursor, but in this case reader.Read () constantly returns false, and I am not getting any data. Another Output variable, however, contains data (a string).

I tried using reader.NextResult (), but also returns false, and my reader.Read () still returns false ...

I can confirm that running proc using Toad returns the correct result set ...

Any ideas? What am I doing wrong?

Thank.

Answer 1

Ok, so after much more digging, I found a solution.

The source code used ExecuteReader. I modified it to run ExecuteNonQuery.

I pass the same parameters and types as I do (2 inputs, 2 outputs, one of which is a string, one is refcursor).

When ExecuteNonQuery is running, I can see the value of the third parameter (row). then I do the following:

OracleDataReader dr = ((OracleRefCursor)pRefcursor.Value).GetDataReader(); 

Then I can make my usual loops with dr! Happy Days!