从SSIS上的脚本任务执行时，Oracle过程未返回结果 [英] Oracle procedure is not returning results when executing from script task on SSIS

问题描述

我正在执行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.

    Private Sub GetClients()

      Dim cmd As New OracleCommand("PKG_HOBS.PRC_HOBS_GET_CLIENTID", FPP1_Connection)
    cmd.CommandType = CommandType.StoredProcedure


    Dim p1 As New OracleParameter(":obus_grp_id", OracleDbType.Int32, ParameterDirection.Output)
    p1.CollectionType = OracleCollectionType.PLSQLAssociativeArray
    p1.Size = 100 ' This is the size of items in array in THIS case
    cmd.Parameters.Add(p1)

    Dim p2 As New OracleParameter(":ostat_c", OracleDbType.Int32, ParameterDirection.Output)
    p2.CollectionType = OracleCollectionType.PLSQLAssociativeArray
    p2.Size = 100 ' This is the size of items in array in THIS case
    cmd.Parameters.Add(p2)

    Dim p3 As New OracleParameter(":ostat_msg_x", OracleDbType.Varchar2, ParameterDirection.Output)
    p3.CollectionType = OracleCollectionType.PLSQLAssociativeArray
    p3.Size = 100 ' This is the size of items in array in THIS case
    p3.ArrayBindSize = Enumerable.Repeat(500, 100).ToArray 
    cmd.Parameters.Add(p3)

    cmd.ExecuteNonQuery()

    Dim oraNumbers1() As OracleDecimal = CType(p1.Value, OracleDecimal())
    Dim myobus_grp_idValues(oraNumbers1.Length - 1) As Integer
    For i As Integer = 0 To oraNumbers1.Length - 1
        myobus_grp_idValues(i) = Convert.ToInt32(oraNumbers1(i).Value)
    Next

    Dim oraNumbers2() As OracleDecimal = CType(p2.Value, OracleDecimal())
    Dim myostat_cValues(oraNumbers2.Length - 1) As Integer
    For i As Integer = 0 To oraNumbers2.Length - 1
        myostat_cValues(i) = Convert.ToInt32(oraNumbers2(i).Value)
    Next

    Dim oraStrings() As OracleString = CType(p3.Value, OracleString())
    Dim myostat_msg_xValues(oraStrings.Length - 1) As String
    For i As Integer = 0 To oraStrings.Length - 1
        myostat_msg_xValues(i) = oraStrings(i).Value
    Next

    Try

        MessageBox.Show(myobus_grp_idValues.ToString)

. . . . . 

包装定义

 TYPE Tnumber IS TABLE OF NUMBER INDEX BY BINARY_INTEGER; 
 TYPE Tmsg_500 IS TABLE OF VARCHAR2(500) INDEX BY BINARY_INTEGER; 

 PROCEDURE prc_hobs_get_clientid (
     obus_grp_id OUT Tnumber, 
     ostat_c OUT Tnumber, 
     ostat_msg_x OUT Tmsg_500);

推荐答案

首先，请勿使用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:

Declare 
    obus_grp_id PKG_HOBS.Tnumber; -- numeric table value
    ostat_c PKG_HOBS.Tnumber;     -- numeric table value
    ostat_msg_x PKG_HOBS.Tmsg_500; -- string table value
BEGIN  
    PKG_HOBS.PRC_HOBS_GET_CLIENTID(obus_grp_id, ostat_c, ostat_msg_x);
END;

我看到您正在执行匿名块-您不需要这样做，因为这会使您的事情变得复杂.您需要做的是使用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)

Dim cmd As New OracleCommand("PKG_HOBS.PRC_HOBS_GET_CLIENTID", conn)
cmd.CommandType = CommandType.StoredProcedure

Dim p1 As New OracleParameter(":p1", OracleDbType.Int64, ParameterDirection.Output)
p1.CollectionType = OracleCollectionType.PLSQLAssociativeArray
p1.Size = 100  ' Declare more than you expect
' This line below is not needed for numeric types (date too???)
' p1.ArrayBindSize = New Integer(99) {} 
cmd.Parameters.Add(p1)

' Add parameter 2 here - same as 1

Dim p3 As New OracleParameter(":p3", OracleDbType.Varchar2, ParameterDirection.Output)
p3.CollectionType = OracleCollectionType.PLSQLAssociativeArray
p3.Size = 100 ' Declare more than you expect
' for string data types you need to allocate space for each element
p3.ArrayBindSize = Enumerable.Repeat(500, 100).ToArray() ' get 100 elements of 500 - size of returning string
' I don't know why you have problems referencing System.Linq but if you do...
'Dim intA() As Integer = New Integer(99) {} 
'For i as integer = 0 to intA.Length -1
'    intA(i) = 500
'Next

cmd.Parameters.Add(p3)
conn.Open()
cmd.ExecuteNonQuery()

' Ora number is not compatible to .net types. for example integer is something 
' between number(9) and (10). So, if number(10) is the type - you get Long in 
' return. Therefore use "Convert" 

' Also, you return arrays, so you need to process them as arrays - NOTE CHANGES


Dim oraNumbers() As OracleDecimal = CType(p1.Value, OracleDecimal())
Dim myP1Values(oraNumbers.Length - 1) As Long
For i as Integer = 0 To oraNumbers.Length - 1
    myP1Values(i) = Convert.ToInt64(oraNumbers(i).Value)
Next

oraNumbers = CType(p2.Value, OracleDecimal())
Dim myP2Values(oraNumbers.Length - 1) As Long
For i as Integer = 0 To oraNumbers.Length - 1
    myP2Values(i) = Convert.ToInt64(oraNumbers(i).Value)
Next    

Dim oraStrings() As OracleString= CType(p3.Value, OracleString())
Dim myP3Values(oraStrings.Length - 1) As String
For i as Integer = 0 To oraStrings.Length - 1
    myP3Values(i) = oraStrings(i).Value
Next

这是最重要的部分

最重要的部分是如何填充声明的类型.让

The most important part is how you fill your declared type. Lets take

TYPE Tnumber IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
v_num Tnumber;

v_num(1) := 1234567890;
v_num(2) := 2345678901;
v_num(3) := 3456789012;

这(上面)将起作用.但这将失败:

This (above) will work. But this will fail:

v_num(0) := 1234567890;
v_num(1) := 2345678901;
v_num(2) := 3456789012;

最后，这将在一个条件下起作用

And finally, this, will work with one condition

v_num(2) := 1234567890;
v_num(3) := 2345678901;
v_num(4) := 3456789012;

在这里我们将在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)

' instead of this 
myP2Values(i) = Convert.ToInt64(oraNumbers(i).Value)
' you will need first to check 
If oraNumbers(i).IsNull Then 
. . . . 

所以，这里的主要作用是，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过程未返回结果的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！