Issue when returning data back from Oracle Database

See more: (untagged)

I have this routine in my code. I call the Stored Proc in Oracle and I *know* I have data in the one column. The oCommand.parameter(Pos - 1).size = 3 as there are 3 records in the array, but when I get to this one particular column, the size suddenly shows as = 0, so the IF STATEMENT checking for size [see comment "Why is this =0?]

The data should be like this for that column for the 3 rows but it is possible that there may not be data in one or more of the rows:

PrevAcctNo
1234567890
null
2531456987

PrevAcctNo is defined as a varchar2.

When I check if SIZE>0, I expect this to be true and it go in and setup the row for the datatable. Instead, it thinks the size =0, so it skips that particular column and goes around to the next column and checks that one.

Can someone help?

Dim oCommand As New OracleCommand
        Dim oParameter As OracleParameter
        Dim Pos As Short
        Dim dt As New DataTable
        Dim rec As Integer
        Dim dr As DataRow
        dt.Locale = CultureInfo.InvariantCulture
        Dim size As Int32 = CInt(pa(0))
        Dim params As String() = pa(1).ToString.Split(CChar(","))

        oCommand.BindByName = False
        oCommand.Connection = m_Connection
        oCommand.CommandText = FunctionName
        oCommand.CommandType = CommandType.StoredProcedure

        oParameter = oCommand.CreateParameter()
        oParameter.Direction = ParameterDirection.Input
        oParameter.OracleDbType = OracleDbType.Int16
        oParameter.Value = CInt(pa(0))
        oCommand.Parameters.Add(oParameter)     'Records

        For Pos = 0 To CShort(params.Length - 1)
            oParameter = oCommand.CreateParameter()
            oParameter.CollectionType = OracleCollectionType.PLSQLAssociativeArray
            oParameter.Direction = ParameterDirection.Output
            oParameter.Value = Nothing
            oParameter.Size = size
            oParameter.ArrayBindSize = BindArray
            oCommand.Parameters.Add(oParameter)
        Next Pos

        Try
            oCommand.ExecuteNonQuery()
        Catch ex As Exception
            Throw
        End Try

        For Pos = 0 To CShort(params.Length - 1)
            dt.Columns.Add(params(Pos))
        Next Pos

        'loop through each record found.  Size is the number of ROWS returned
        For rec = 0 To size - 1
            dr = dt.NewRow

            Dim parameterAdded As Boolean = False

            For Pos = 0 To CShort(params.Length - 1)

                '***** this IF STATMENT is where the discrepancy is happening *****
                If oCommand.Parameters(Pos + 1).Size > 0 Then  '<--why is this = 0?
                   Dim obj As New Object
                   obj = oCommand.Parameters(Pos + 1).Value
                   
                   '[not shown but here is where dr is setup]
                   
                End If
            Next Pos
            If parameterAdded Then
                dt.Rows.Add(dr)
            End If
        Next rec
        ExecutePackageGet = dt