ODP.NET

Guys and Girls,

I desperately need some help with something. I'm referencing a stored procedure for the first time in a VB.NET app I'm writing and having a problem with an error. I'm pretty new to all this, but there seems to be some issue with the data types that I'm using in the code:

Imports System
Imports System.Data
Imports Oracle.DataAccess.Types
Imports Oracle.DataAccess.Client

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 = 20
pStatusText.Size = 50
pRecReturned.Size = 50
pMsgSysNameList.Size = 50
pMsgLastInDateTime.Size = 50
pMsgGoodIn.Size = 50
pMsgBadIn.Size = 50
pMsgLastOutDateTime.Size = 50
pMsgGoodOut.Size = 50
pMsgBadOut.Size = 50

pStatus.ArrayBindSize = New Int32 (20) **error thrown at this line. The parenthesis should be square brackets here but is marked up as an underline when I paste the text here, so I've left it with the parenthesis **


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 pMsgGoodIn.Size - 1
Console.WriteLine(pMsgGoodIn.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


The error it throws is:

- Value of type 'Integer' cannot be converted to '1-dimensional array of Integer'.
- identifier expected.