First of all, don't use OleDb, period. Microsoft tells you to use vendor-specific provider. Use Oracle's ODP.NET.
Second, to retrieve recordset from Oracle SP, you need to return refCursor.
Edit: 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;
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.
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
And this is Most Important part
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;
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
. . . .
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
Here is some reference
