问题描述
我正在执行 Oracle 过程,该过程具有三个 OUTPUT 参数并在表类型变量中返回结果.
I'm executing Oracle procedure, which has three OUTPUT parameters and returns results in table type variable.
这里的限制是,我不应该使用 ODBC、MSDAORA 提供程序来调用程序.所以我打算使用 Oracle OLEDB 提供程序.
Here the limitations are, i should not use ODBC, MSDAORA providers to call the procedure. So I'm planning to using Oracle OLEDB provider.
我能够成功执行该过程,但是当我进行检查时(同时 dr.Read())它没有返回任何记录.但我知道根据存储过程结果,它应该返回 66 条记录.
I'm able to execute the procedure successfully, but when i do check (while dr.Read()) its not returning any records. But I know as per stored procedure results, it should return 66 records.
我怀疑我的 Vb.net 代码......请提出建议......提前致谢.
I doubt about my Vb.net code.... Please suggest something.. Thanks in advance.
包定义
推荐答案
首先,不要使用OleDb
,句号.Microsoft 告诉您使用供应商特定的提供程序.使用 Oracle 的 ODP.NET.
First of all, don't use OleDb
, period. Microsoft tells you to use vendor-specific provider. Use Oracle's ODP.NET.
其次,要从 Oracle SP 检索记录集,您需要返回 refCursor
.
Second, to retrieve recordset from Oracle SP, you need to return refCursor
.
此时我们知道您的参数是表格.要处理此问题,您需要将 p.CollectionType = OracleCollectionType.PLSQLAssociativeArray
添加到您的参数
At this time we know that your parameters are tables. To process this you need to add p.CollectionType = OracleCollectionType.PLSQLAssociativeArray
to your parameters
您的代码本质上是这样的:
Your code is essentially this:
我看到你在执行匿名块 - 你不需要这样做,因为这会让事情复杂化.你需要做的是使用vb.net直接执行包.
I see you executing anonymous block - you don't need to do this as this complicates things to you. What you need to do is use vb.net to execute package straight.
底线:您当前的 ORACLE 代码不会将结果输出到 .NET.删除匿名块,您就可以开展业务了.
Bottom line: your current ORACLE code does nothing to output results to .NET. Remove anonymous block and you're in business.
这是处理您的程序类型的代码(在评论中阅读)
Here is the code to process your type of procedure (read in comments)
这是最重要的部分
最重要的部分是如何填充声明的类型.让我们来
The most important part is how you fill your declared type. Lets take
这(上面)会起作用.但这会失败:
This (above) will work. But this will fail:
最后,这将在一个条件下起作用
And finally, this, will work with one condition
在这里,我们将在 p1.Value
中获得 4 个成员,但在索引 0
下,您将拥有 oracle null
.所以,你需要在这里处理它(如果你有这种情况)
Here we will get 4 members in p1.Value
but under index 0
you will have oracle null
. So, you would need to deal with it here (if you have such condition)
所以,这里的主要问题是,你的 pl/sql 表的索引是什么?!它需要从大于 0
的东西开始,最好从 1
开始. 如果你有跳过数字的索引,即 2,4,6,8
,所有这些空格都将成为返回oracle数组的一部分,其中将有oracle null
So, the principal thing here is, WHAT is the index of your pl/sql table?! It needs to start from something larger than 0
, and preferably from 1
. And if you have index with skipped numbers, i.e. 2,4,6,8
, all those spaces will be part of returning oracle array and there will be oracle null
in them
这里有一些参考
这篇关于从 SSIS 上的脚本任务执行时,Oracle 过程不返回结果的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持跟版网!