Array from Oracle stored procedure

[LNM]

I need to get an array returned from the Oracle stored procedure. Did anyone do something like that? Amy links? Any information on adArray?
Any advice will be greatly appreciated.

Lana

[Iouri]

'Here is the code how you can pass array as parameter

Dim aryTest(1 To 3)

Private Sub Command1_Click()
TestArray aryTest
TestArray
End Sub

Private Sub Form_Load()
aryTest(1) = 1
aryTest(2) = 2
aryTest(3) = 3
End Sub

Private Sub TestArray(Optional aryToTest)
If IsMissing(aryToTest) Then
MsgBox "Array Not Passed"
Else
For intCount = 1 To 3
strMessage = strMessage & "Element " & CStr(intCount) & " = " & CStr(aryToTest(intCount)) & vbLf
Next
MsgBox strMessage
End If
End Sub





Iouri Boutchkine
[email protected]

[Iouri]

Try this also - the function that returns arry

Private Function Average(ParamArray arguments() As Variant) As Single
Dim i As Integer
Dim total As Single

For i = LBound(arguments) To UBound(arguments)
total = total + arguments(i)
Next i
Average = total / (UBound(arguments) - LBound(arguments))
End Function

Private Sub Command1_Click()
MsgBox Average(1, 2, 3, 4, 5)
End Sub




Iouri Boutchkine
[email protected]

[LNM]

The problem is not to pass the array but to get the array from the stored procedure. I am having the problem in communication between the VB data types and Oracle data types. I am getting the "Type name is invalid" error. And it seems that it's coming from the OLEDB provider.
The code I was using to call the stored procedure:
Private Sub Command1_Click()
Dim cn As New ADODB.Connection
Dim cmd As New ADODB.Command
Dim prm1 As New ADODB.Parameter, prm2 As New ADODB.Parameter
Dim strConnect As String, param1() As String, param2 As Integer
Dim iNumber As Integer

strConnect = "Data Source=IMSD; User Id=IMS; Password=IMS"
cn.Open strConnect

'Create parameters
With prm1
.Direction = adParamOutput
.Type = adArray + adVarChar
.Value = param1()
End With

With prm2
.Direction = adParamOutput
.Type = adNumeric
.Value = param2
End With

With cmd
.CommandText = "temp_array_test_pk.test"
.CommandType = adCmdStoredProc
.ActiveConnection = cn
.Parameters.Append prm1
.Parameters.Append prm2
End With

'Call the stored procedure
cmd.Execute
iNumber = cmd.Parameters(1)

End Sub