Stored proc: Type name is invalid

[PongPing]

Hi,
i got problems with passing arrays or variants to stored procedures. When i run this code against Oracle 8 I just get the error: 'Type name is invalid'
Why?



Dim CON As New ADODB.Connection
Dim QSQL As String
Dim COM As New ADODB.Command
Dim Param1 As ADODB.Parameter
Dim RS As New ADODB.Recordset

Dim a(255) As Long, i As Long

' Connect Database
CON.ConnectionString = "provider=MSDAORA;DATA SOURCE=x;USER ID=y;PASSWORD=z"
CON.CursorLocation = adUseServer
CON.Open

' Prepare stored procedure
COM.ActiveConnection = CON
COM.CommandText = "package.procedure"
COM.CommandType = adCmdStoredProc

' Prepare parameters
Set Param1 = COM.CreateParameter("Input", adArray Or adInteger, adParamInput)
COM.Parameters.Append Param1

For i = 1 To 255
a(i) = i
Next i
COM.Parameters(0).Value = a

' Execute Procedure
RS.CursorLocation = adUseServer
RS.CursorType = adOpenKeyset
RS.LockType = adLockReadOnly
Set RS = COM.Execute


The procedure is defined like this:
TYPE T_NumList IS VARRAY(255) OF NUMBER;
PROCEDURE ProcName(ar IN T_NumList);

 

[Antzz]

Well, I am not sure if you can use the following syntax :

Set Param1 = COM.CreateParameter("Input", adArray Or adInteger, adParamInput

You are using an "OR" to specify that the datatype can be either an array or an integer. I guess, you can only specify one data type to one parameter.

 

[PongPing]

Thankz Antz,

i appreciate your anticipation but:
i actually used the or operator to add the two values.
this is to make clear its an array of integers....

;-)