从同一存储过程获取RefCursor和VarChar输出 [英] Getting RefCursor and VarChar outputs from the same Stored Procedure

问题描述

我是Oracle的新手，请保持柔和...

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

我正在.NET 4中使用C#和ODP11查询现有的Oracle DB.到目前为止，到目前为止，所有过程都很难弄清楚，但是一旦我弄清楚自己在做什么，大多数过程就奏效"了. ...这是一个不同的故事...

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...

我有一个存储的proc，它带有4个参数:2个输入和2个输出... 2个输入很大.输出之一是Varchar2，另一个是反射器.

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.

我正在使用OracleDataReader来运行查询，就像我与其他任何返回RefCursor的proc一样，但是在此过程中，reader.Read()始终返回false并且我没有数据.但是，另一个Output变量确实包含数据(字符串).

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).

我尝试使用reader.NextResult()，但是它也返回false，而我的reader.Read()仍然返回false ...

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

我可以确认使用Toad运行proc会返回正确的结果集...

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

有什么想法吗?我究竟做错了什么?

Any ideas? What am i doing wrong?

谢谢.

推荐答案

好，因此，在进行了更多挖掘之后，我找到了解决方案.

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

原始代码使用的是ExecuteReader.我将其更改为运行ExecuteNonQuery.

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

我传递的参数和类型与我输入的参数和类型相同(2个输入，2个输出，其中一个是字符串，一个是反射器).

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

当ExecuteNonQuery运行时，我可以看到第三个参数(字符串)的值.然后我执行以下操作:

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!

这篇关于从同一存储过程获取RefCursor和VarChar输出的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！