[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

ArrayBindSize and Size for PLSQLAssociativeArrays

Posted on 2010-11-23
4
Medium Priority
?
1,574 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 2000 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

On Demand Webinar - Networking for the Cloud Era

This webinar discusses:
-Common barriers companies experience when moving to the cloud
-How SD-WAN changes the way we look at networks
-Best practices customers should employ moving forward with cloud migration
-What happens behind the scenes of SteelConnect’s one-click button

Question has a verified solution.

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

I remember the day when someone asked me to create a user for an application developement. The user should be able to create views and materialized views and, so, I used the following syntax: (CODE) This way, I guessed, I would ensure that use…
Background In several of the companies I have worked for, I noticed that corporate reporting is off loaded from the production database and done mainly on a clone database which needs to be kept up to date daily by various means, be it a logical…
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.
Suggested Courses

656 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