Oracle存储过程在从SSIS脚本任务执行时未返回结果

Question

Oracle存储过程在从SSIS脚本任务执行时未返回结果

vb.netoraclestored-proceduresssis

3

我正在执行一个包含三个输出参数且返回表类型变量结果的Oracle存储过程。

这里的限制是，我不能使用ODBC、MSDAORA提供程序调用该存储过程。所以我计划使用Oracle OLEDB提供程序。

我能够成功执行该存储过程，但当我检查（while dr.Read()）时，它没有返回任何记录。但是，根据存储过程的结果，它应该返回66条记录。

我对我的Vb.net代码有所怀疑...请建议一些解决方法。谢谢您提前。

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

- ScriptGuy

请检查我的最终答案 - 它有效！ - T.S.

我使用了system.linq来使程序工作。但是我仍然遇到了这个错误：Oracle.DataAccess.Client.OracleException ORA-01455: converting column overflows integer datatype 请检查我在问题中的最新代码。 - ScriptGuy

您太棒了T.S.，在整个交流和协助过程中我真的要感谢您，使这一切都能顺利进行。真是太棒了，您是天才！这就是我想说的... - ScriptGuy

在我的答案底部，我详细讨论了这个问题-> If oraNumbers(i).IsNull Then。我认为，你真正应该做的是 dim list as new List(of string)(oraStrings.Length) 然后在for循环中执行 If not oraStrings(i).IsNull Then list.Add(oraStrings(i).Value) 然后，如果你想要数组，就用list.Toarray() - T.S.

是的，它起作用了...你又棒又厉害...大师... - ScriptGuy

显示剩余4条评论

1个回答

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- T.S. · Accepted Answer

首先，无论什么情况下都不要使用OleDb。微软建议使用供应商特定的提供程序。使用Oracle的ODP.NET。

其次，要从Oracle SP检索记录集，需要返回refCursor。 编辑:现在我们知道你的参数是表。为了处理这个问题，你需要将p.CollectionType = OracleCollectionType.PLSQLAssociativeArray添加到你的参数中。

你的代码本质上是这样的：

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直接执行包。

底线:你当前的ORACLE代码没有输出结果到.NET。删除匿名块，你就可以开始工作了。

这是处理您类型过程的代码（请阅读注释）

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

这是最重要的部分

最重要的部分是如何填写您声明的类型。让我们来看一个例子：

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;

这个（上面）会起作用。但是这个会失败：

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

最后，这将按一个条件起作用。

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

在这里，我们将会得到p1.Value中的4个成员，但是在索引0下，你将会得到oracle null。因此，你需要在这里处理它（如果你有这样的条件）。

' 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。参考这里。