1

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

I am querying an existing Oracle DB using C# and ODP11 in .NET 4. All procedures up to this point have been, well, painful to figure out at first, but mostly "just worked" once i figured what i was doing... this one is a different story...

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

I am using an OracleDataReader to run the query, like i have with any other proc that returns a RefCursor, but in this one, reader.Read() constantly returns false and i get no data. The other Output variable does, however, contain data (a string).

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

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

Any ideas? What am i doing wrong?

Thanks.

TiernanO
  • 1,597
  • 14
  • 31

1 Answers1

1

Ok, so after a lot more digging, i found the solution.

The original code was using ExecuteReader. I have changed it to run ExecuteNonQuery.

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

When ExecuteNonQuery runs, i can see the value of the 3rd param (the string). then i do the following:

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

Then i can do my usual loops with the dr! Happy days!

TiernanO
  • 1,597
  • 14
  • 31
  • Can you post a link to where you got that code from? – H.Wolper Feb 19 '15 at 06:53
  • no idea where i got that... it was 18 months or so since i even looked at that code. I do know that still works and is still in production! – TiernanO Feb 20 '15 at 10:57
  • @TiernanO kindly can you help me with this http://stackoverflow.com/questions/41306344/how-to-execute-multiple-oracle-query-c-sharp/41308024?noredirect=1#comment69821736_41308024 – sam Dec 24 '16 at 00:40