Solved

ArrayBindSize and Size for PLSQLAssociativeArrays

Posted on 2010-11-23
4
1,492 Views
Last Modified: 2013-11-05
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

Open in new window


 
 

0
Comment
Question by:khanfe
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
4 Comments
 
LVL 15

Accepted Solution

by:
Franck Pachot earned 500 total points
ID: 34480597
Hi,
As you don't know the size of the output, you should not return then on one call only. You should design your procedure to get for example 100 next rows each time it is called.
Or you can see if your procedure can return a refcursor, so that you fetch rows as you do with a select statement.
Regards,
Franck.
0
 

Author Comment

by:khanfe
ID: 34662833
Hi,

Thanks for your inpur franckpachot.  Since the Oracle procedure was written by a developer, I'll need to go back to him with your recommendation.  This has been a difficult question for most - let's see what happens?
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
Real-time is more about the business, not the technology. In day-to-day life, to make real-time decisions like buying or investing, business needs the latest information(e.g. Gold Rate/Stock Rate). Unlike traditional days, you need not wait for a fe…
Via a live example, show how to take different types of Oracle backups using RMAN.
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.

738 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question