ArrayBindSize and Size for PLSQLAssociativeArrays

Hey there,

I'm writing a VB.NET app and a complete newbie to ODP.NET.  I was hoping someone may be able to help with an issue. I basically need to call an SP from a VB.NET application and got as far as I could with the code below. The problem is that the parameters associates to this SP are all direction output. As a result, i'm having trouble trying to use the ArrayBindSize property and the Size property since I don't know what the number/size of the output from running the SP will exactly be? Does anyone see my predicament?

I'm also seeing the following errors when I try to compile my application:

Value of type '1-dimensional array of String' cannot be converted to '1-dimensional array of Integer' because 'String' is not derived from 'Integer'.

Thanks in advance!

Public Function RunSPReturnDS()

Try

Dim oraConnString As String = "Data Source=*****;User Id=******;Password=*****;"
Dim oraConnection As New OracleConnection(oraConnString)
oraConnection.Open()

MessageBox.Show("Connection works!", "SUCCESS", MessageBoxButtons.OK, MessageBoxIcon.Exclamation)

oracleCMD = oraConnection.CreateCommand

oracleCMD.CommandType = CommandType.StoredProcedure
oracleCMD.CommandText = "OCDI_UTILITIES.GET_EXISS_TODAY"

pStatus = New OracleParameter("P_STATUS", OracleDbType.Int32)
pStatusText = New OracleParameter("P_STATUS_TEXT", OracleDbType.Varchar2)
pRecReturned = New OracleParameter("P_RECORDS_RETURNED", OracleDbType.Int32)
pMsgSysNameList = New OracleParameter("P_MSG_SYSTEM_NAME_LIST", OracleDbType.Varchar2)
pMsgLastInDateTime = New OracleParameter("P_MSG_LAST_IN_DATETIME_LIST", OracleDbType.Date)
pMsgGoodIn = New OracleParameter("P_MSG_GOOD_IN_LIST", OracleDbType.Int32)
pMsgBadIn = New OracleParameter("P_MSG_BAD_IN_LIST", OracleDbType.Int32)
pMsgLastOutDateTime = New OracleParameter("P_MSG_LAST_OUT_DATETIME_LIST", OracleDbType.Date)
pMsgGoodOut = New OracleParameter("P_MSG_GOOD_OUT_LIST", OracleDbType.Int32)
pMsgBadOut = New OracleParameter("P_MSG_BAD_OUT_LIST", OracleDbType.Int32)

pStatus.CollectionType = OracleCollectionType.PLSQLAssociativeArray
pStatusText.CollectionType = OracleCollectionType.PLSQLAssociativeArray
pRecReturned.CollectionType = OracleCollectionType.PLSQLAssociativeArray
pMsgSysNameList.CollectionType = OracleCollectionType.PLSQLAssociativeArray
pMsgLastInDateTime.CollectionType = OracleCollectionType.PLSQLAssociativeArray
pMsgGoodIn.CollectionType = OracleCollectionType.PLSQLAssociativeArray
pMsgBadIn.CollectionType = OracleCollectionType.PLSQLAssociativeArray
pMsgLastOutDateTime.CollectionType = OracleCollectionType.PLSQLAssociativeArray
pMsgGoodOut.CollectionType = OracleCollectionType.PLSQLAssociativeArray
pMsgBadOut.CollectionType = OracleCollectionType.PLSQLAssociativeArray

pStatus.Direction = ParameterDirection.Output
pStatusText.Direction = ParameterDirection.Output
pRecReturned.Direction = ParameterDirection.Output
pMsgSysNameList.Direction = ParameterDirection.Output
pMsgLastInDateTime.Direction = ParameterDirection.Output
pMsgGoodIn.Direction = ParameterDirection.Output
pMsgBadIn.Direction = ParameterDirection.Output
pMsgLastOutDateTime.Direction = ParameterDirection.Output
pMsgGoodOut.Direction = ParameterDirection.Output
pMsgBadOut.Direction = ParameterDirection.Output

pStatus.Size = 10
pStatusText.Size = 10
pRecReturned.Size = 10
pMsgSysNameList.Size = 10
pMsgLastInDateTime.Size = 10
pMsgGoodIn.Size = 10
pMsgBadIn.Size = 10
pMsgLastOutDateTime.Size = 10
pMsgGoodOut.Size = 10
pMsgBadOut.Size = 10

'pStatus.ArrayBindSize = New Int32(10) {100, 100, 100, 100, 100, 100, 100, 100, 100, 100, 100}
'pStatusText.ArrayBindSize = New Int32(10) {100, 100, 100, 100, 100, 100, 100, 100, 100, 100, 100}
'pRecReturned.ArrayBindSize = New Int32(10) {100, 100, 100, 100, 100, 100, 100, 100, 100, 100, 100}
'pMsgSysNameList.ArrayBindSize = New Int32(10) {100, 100, 100, 100, 100, 100, 100, 100, 100, 100, 100}
'pMsgLastInDateTime.ArrayBindSize = New Int32(10) {100, 100, 100, 100, 100, 100, 100, 100, 100, 100, 100}
'pMsgGoodIn.ArrayBindSize = New Int32(10) {100, 100, 100, 100, 100, 100, 100, 100, 100, 100, 100}
'pMsgBadIn.ArrayBindSize = New Int32(10) {100, 100, 100, 100, 100, 100, 100, 100, 100, 100, 100}
'pMsgLastOutDateTime.ArrayBindSize = New Int32(10) {100, 100, 100, 100, 100, 100, 100, 100, 100, 100, 100}
'pMsgGoodOut.ArrayBindSize = New Int32(10) {100, 100, 100, 100, 100, 100, 100, 100, 100, 100, 100}
'pMsgBadOut.ArrayBindSize = New Int32(10) {100, 100, 100, 100, 100, 100, 100, 100, 100, 100, 100}

pStatus.ArrayBindSize = BindArraysToSizeInt()
pStatusText.ArrayBindSize = BindArraysToSizeStr()
pRecReturned.ArrayBindSize = BindArraysToSizeInt()
pMsgSysNameList.ArrayBindSize = BindArraysToSizeStr()
pMsgLastInDateTime.ArrayBindSize = 
pMsgGoodIn.ArrayBindSize = BindArraysToSizeInt()
pMsgBadIn.ArrayBindSize = BindArraysToSizeInt()
pMsgLastOutDateTime.ArrayBindSize = 
pMsgGoodOut.ArrayBindSize = BindArraysToSizeInt()
pMsgBadOut.ArrayBindSize = BindArraysToSizeInt()

oracleCMD.Parameters.Add(pStatus)
oracleCMD.Parameters.Add(pStatusText)
oracleCMD.Parameters.Add(pRecReturned)
oracleCMD.Parameters.Add(pMsgSysNameList)
oracleCMD.Parameters.Add(pMsgLastInDateTime)
oracleCMD.Parameters.Add(pMsgGoodIn)
oracleCMD.Parameters.Add(pMsgBadIn)
oracleCMD.Parameters.Add(pMsgLastOutDateTime)
oracleCMD.Parameters.Add(pMsgGoodOut)
oracleCMD.Parameters.Add(pMsgBadOut)

oracleCMD.ExecuteNonQuery()

For i As Integer = 0 To pStatus.Size - 1
Console.WriteLine(pStatus.Value(i))
Next

'ListView1.Items(3).SubItems.Add(P_STATUS)

pStatus.Dispose()
pStatusText.Dispose()
pRecReturned.Dispose()
pMsgSysNameList.Dispose()
pMsgLastInDateTime.Dispose()
pMsgGoodIn.Dispose()
pMsgBadIn.Dispose()
pMsgLastOutDateTime.Dispose()
pMsgGoodOut.Dispose()
pMsgBadOut.Dispose()
oracleCMD.Dispose()
oraConnection.Dispose()

Catch ex As Exception

MessageBox.Show(ex.Message.ToString(), "ERROR", MessageBoxButtons.OK, MessageBoxIcon.Error)
End Try

End Function

Private Function BindArraysToSizeInt() As Integer()
Dim ArrBindSizeInt(collectionObjectsInt.Count - 1) As Integer
For i As Integer = 0 To collectionObjectsInt.Count - 1
ArrBindSizeInt(i) = collectionObjectsInt(i).ToString().Length
Next i
Return ArrBindSizeInt
End Function

Private Function BindArraysToSizeStr() As String()
Dim ArrBindSizeStr(collectionObjectsStr.Count - 1) As String
For i As Integer = 0 To collectionObjectsStr.Count - 1
ArrBindSizeStr(i) = collectionObjectsStr(i).ToString().Length
Next i
Return ArrBindSizeStr
End Function

End Class

Select allOpen in new window