I'm executing Oracle procedure, which has three OUTPUT parameters and returns results in table type variable.
Here the limitations are, i should not use ODBC, MSDAORA providers to call the procedure. So I'm planning to using Oracle OLEDB provider.
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.
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)
. . . . .
Package definition
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);
See Question&Answers more detail:os